how to avoid duplicate data

[madwoman]

I have inherited a 5 table database which is associated with membership details and all tables are linked via the membership number which appears in every table..eg
Main Register
membernumber, surname, forename, age etc etc etc
Home address
membernumber, address1, address2, address3, postcode
Business address
membernumber, company name, address1, address2,etc etc etc
Subscription
membernumber, grade, subscription, bank account, etc etc
Training
membernumber, college, results, etc etc

I am informed that it is bad practice to have duplicate data in multiple tables (ie. membernumber field) as updating the data item would be difficult (eg. if the members number needed to be changed for any reason). If this is the case how would I restructure the tables but maintain the link through the membernumber?

I am fairly new to Access....as you can no doubt tell.

 

[KeithG]

Just with what you posted above I don't see anything wrong with your table design. It is bad practice to store duplicate data if it is unnessary and I would suggest that you do a little research on Normalization.

 

[wazz]

data that could be duplicated are "key" fields, such as membernumber (fields that uniquely identify the row you're on).

membernumber is a primary key (PK) in the Main Register and a foreign key (FK) in all other tables (it is still a key field but foreign to those tables).

the foreign key is used to refer back to the PK (table) so you know who you are talking about (!) without duplicating all the personal data. you need some unique reference data: the key.

hth.

(sry keith, i can't sleep).

 

[The_Doc_Man]

madwoman, the admonishment about "not storing duplicate data" is because in an improper table, someone would store the member's name in every table.

If the member has a number, using the member number prevents you from having to store the longer member name. Numbers usually take up 2 or 4 bytes. Names take up 20-30 bytes. It is the latter that you don't want to duplicate in your secondary tables. Keeping the member number is like storing a pointer to the member. Then you can follow the pointer when you are looking at one of the other tables and need to look up something about the particular member. (This pointer is the basis for the term "relational" database, because the pointer is the tangible expression of a record's relationship to a record in another table.)

 

[wazz]

you might still be wondering if you could combine some of this stuff. if it is already set up then there wouldn't be any point because it's fine.

you could possibly combine the main register and the home address, for example, but if there is any (!) chance that a member might have more than one address then combining the tables would be a mistake. same goes for business address and the rest. if there's ANY chance that a member might have more than one business address (or home), or more than one subscription or more training than from just one school then then tables must stay as they are: the relationship between the main register and the other tables is One to Many (one member may have many business addresses, subscriptions, training).