1

I'm designing a database that holds information on suppliers, clients, users, client sites etc that all have address data. I have elected to use three standard address lines, town/city, county and postcode fields.

My question is, would it be better to have these fields in all the tables that require them or have a address table and just link the address id to the relavent table?

Many Thanks

Gavin.

Gavin
  • 711
  • 1
  • 5
  • 13

1 Answers1

0

If it's possible for multiple fields to have the same address, I'd put the addresses in their own table. This helps prevent insertion/update anomalies, among other things. If every address is unique, it might not be that important.

In general, a rule of thumb is "never repeat data". So, if multiple rows have the same values, there's a chance those values can be moved into their own table.

AdamMc331
  • 16,492
  • 10
  • 71
  • 133
  • Thanks McAdam. I know the general rule is not to repeat data, but was unsure if that rule extended to the field names? – Gavin Oct 07 '15 at 16:23
  • I'm not sure what you mean? What I mean by repeat data is that if there are two clients who live at "12345 Blueberry Lane" then you might want to put addresses in their own table and link to them, so that "12345 Blueberry Lane" only appears in one row somewhere, and not in two rows. – AdamMc331 Oct 07 '15 at 16:26
  • Sorry, what I meant was having say the postcode or street field in serveral different tables. – Gavin Oct 07 '15 at 16:39
  • 1
    No. I think just having `| id | street address | city | state | zip |` is enough for one table. Some people will say you can move city state zip into it's own table to increase normalization (since a city, state combination can only have a single zip code) but I do not think that's always necessary. Anyways, using this address table you can just link to it from your first one using a column like `fk_address`. – AdamMc331 Oct 07 '15 at 16:40