Fixing an existing database

Fixing an existing database

Post by SmVubn » Wed, 11 Jun 2008 01:52:01


I'm a newbie, forgive me. I know just enough to get myself in trouble.

I've designed a simple database for tracking pawn slips. My fields are:

Last Name, First Name, Middle Name, Birthdate, Item Name, Item Description,
Serial Number, Pawn Shop, Transaction Date, Amount, Transaction Number

Now that I look at it, one person may have several entries so the name
repeats itself. How do I change the current database to tie in all the
transactions of the same individual together?

Thanks.
 
 
 

Fixing an existing database

Post by Jeff Boyc » Wed, 11 Jun 2008 02:01:40

Jenny

The structure you described would be perfect ... if you were using a
spreadsheet! Congratulations for recognizing that Access has a better way.

You might want to read up on normalization and relational database design
for more ideas, and check the sample (Northwinds) database that comes with
Access.

It sounds like you have Persons, Shops, and Items.

One approach might be to have:

tblPerson
PersonID
FirstName
MiddleName
LastName
DOB

tblStore
StoreID
StoreName
DeliveryAddr
City
State_Province
PostalCode

tblItem
ItemID
ItemName
ItemSerialNumber
(other data elements to help you uniquely identify an item)

tblTransaction
TransactionID
PersonID
ItemID
StoreID
TransactionDate
TransactionAmount
(?TransactionType?)
(other data elements related to the transaction)

Note that with an approach like this, you are only using the PersonID, not
the entire name, etc.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 
 
 

Fixing an existing database

Post by S0FSTCBERV » Wed, 11 Jun 2008 02:07:03

Patron - Autonumber - primary key, Last Name, First Name, Middle Name,
Birthdate, etc.

Patron - number - integer - foreign key, Transaction Number, Item Name, Item
Description, Serial Number, Pawn Shop, Transaction Date, Amount, Redeemed
date.
Set relationships one-to-many from Person to Pawn on Patron. Use a
form/subform for Person to Pawn.
--
KARL DEWEY
Build a little - Test a little
 
 
 

Fixing an existing database

Post by S2VuIFNoZX » Wed, 11 Jun 2008 03:10:00

eff has given you a good solution for 'normalizing' your database. As
background you might find the following brief summary of what normalization
means helpful. Its not meant to be an exhaustive description of the subject,
and doesn't deal with some of the more advanced aspects, but I hope it helps
you gain an understanding of the main principles:

"Normalization is the process of eliminating redundancy from a database, and
involves decomposing a table into several related tables. In a relational
database each table represents an entity type, e.g. Contacts, Companies,
Cities, States etc. and each column in a table represents an attribute type
of the entity type, e.g. ContactID, FirstName and LastName might be attribute
types of Contacts and hence columns of a Contacts table. Its important that
each attribute type must be specific to the entity type, so that each 'fact'
is stored once only. In the jargon its said that the attribute type is
'functionally dependent' solely on the whole of the primary key of a table.

To relate tables a 'referencing' table will have a foreign key column which
makes the link to the 'referenced' table, e.g. a Contacts table might have a
CompanyID column as a foreign key, while a Companies table has a CompanyID
column as its primary key. Consequently no data other than the CompanyID
needs to be stored in a row in the Contacts table for all the company
information for that contact to be known; its available via the relationship
and can be returned in a query joining the two tables on the CompanyID
columns.

Similarly the Companies table might have a CityID column and the Cities
table a StateID column. If its an international database the States (or more
generically Regions) table would have a CountryID referencing the primary key
of a Countries table. So via the relationships, simply by entering (in
reality this would be selected from a list of Companies in a combo box, not
typed in)a CompanyID in the Contacts table the location of the contact's
company is also known. Redundancy, and therefore repetitive data entry is
avoided.

To see how a database is made up of related tables take a look at the sample
Northwind database. Its not completely normalized in fact (deliberately so
for the sake of simplicity) but illustrates the main principles of how tables
representing entity types relate to each other. An example of its lack of
proper normalization can be found in its Customers table. You'll see that
this has City, Region and Country columns so we are told numerous times that
S茫o Paulo is in SP region (as is Resende) and that SP region is in Brazil.
Not only does this require repetitive data entry, but more importantly it
opens up the risk of 'update anomalies', e.g. it would be perfectly possible
to put S茫o Paulo in California in one row and California in Ireland! Proper
normalization as I described above would prevent this as the fact that S茫o
Paulo is in SP region would be stored only once in the database as would the
fact that SP region is in Brazil and California is in the USA.

An example of what at first sight might seem to be redundancy, but in fact
is not, can also be found in Northwind. The Products table and the
OrderDetails table both have UnitPrice columns. It might be thought that the
unit price of a product could always be looked up from the Products table, so
its unnecessary in Order Detai