Acceptable number of duplicates in a Table

[PhilOfWalton]

As a matter of interest, how many duplicated words do you suggest are acceptable in a table?

For example, if I am writing a Db for a local business or organisation, I know that the same town will appear in the address numerous times, so there are 3 tables for address, towns and counties/states.

If, on the other hand. the Db is for a more national organisation, then I tend to include the town & county as fields in the address table.

So the question is, in the later case, how many duplicate towns is sensible. I tend to limit it to about half a dozen, but would be interested in the Experts views.

Phil

[twinnyfo]

Phil,

Although your question is off topic, the topic of the question seems on topic. I aim to never have duplicates, if at all possible. In your example, I would have a table for “People” and a table for “Addresses”. This would allow you to have Personal, Work and alternate addresses (as many as you like) for each person. In the Addresses table, I would have separate tables for each street name, city and state. Some would have a separate table for “Street Type” such as “Street”, “Road”, “Avenue”, etc.

I hope this makes sense.

[PhilOfWalton]

Definitely off topic. I'd be more than happy if this could me moved to a new topic.

Phil

[PhilOfWalton]

Thanks, Twinnyfo for moving this to a new topic, very sensible and much appreciated.

Interesting. I tend to allow a short 0.5% duplicates and a maximum of about 10 duplicates for a large address table before I consider adding the town & county table. Don't think I have ever got to that sort of limit with street names.

I tend to use a lot of join tables - they certainly give great flexibility. as you say, so many learners get the idea of an AddressID in their People Table, then realise that there is a business address so add AddressID, and curse when the find out about the holiday home.

I am trying to write a beginners guide to database design and in particular a bit about normalisation, because so many of the Dbs we see on Bytes are just hopeless. Do you think this would be of any use?

Phil

[twinnyfo]

Absolutely a great idea. There is an article on Bytes that talks about normalization but it speaks very theoretically and might not be as straightforward as a beginner might need. I’d be glad to take a look at your ideas!