CODEWITHSUNDEEP
sqldatabasedatabase-designdata-modelingdenormalization
Ben
Ben

Reputation: 16533

Is denormalizing acceptable in this case?

I have the following locations table:

----------------------------------------------------------
| ID | zoneID | storeID | address | latitude | longitude |
----------------------------------------------------------

and the phones table:

-----------------------
| locationID | number |
-----------------------

Now, keep in mind that for any giving store it can be up to five phone numbers, top. Order doesn't matter.

Recently we needed to add another table which would contain stores related info which would also include phone numbers.

Now, to this new table doesn't apply locationID so we can't store the phones in the previous phone table.

Keeping the DB normalized would require, in the end, 2 new tables and a total of 4 joins to retrieve the data. Denormalizing it would render the old table like:

----------------------------------------------------------------------------------
| ID | zoneID | storeID | address | latitude | longitude | phone1 | ... | phone5 |
----------------------------------------------------------------------------------

and having a total of 2 tables and 2 joins.

I'm not a fan of having data1, data2, data3 fields as it can be a huge pain. So, what's your opinion.

Upvotes: 0

Views: 235

Answers (3)

James Anderson
James Anderson

Reputation: 27478

I think your problem stems from an erroneous model.

Why do you have a location id and a store id? Can a store occupy more than one location? Is the phone number tied to a geographic location?

Just key everything by StoreId and your problems will disappear.

Upvotes: 4

Saket Bansal
Saket Bansal

Reputation: 156

just try to relate your new table with old location table, as both the tables represent the store you should be able to find someway to relate both. if you can do that your problem is solved, because than you can keep using phone table as before.

Related the new table with old location table will help you beyond getting phone numbers

Upvotes: 0

paxdiablo
paxdiablo

Reputation: 881153

My opinion, for what it's worth, is that de-normalisation is something you do to gain performance if, and only if, you actually have a performance problem. I always design for 3NF and only revert if absolutely necessary.

It's not something you do to make your queries look nicer. Any decent database developer would not fear a moderately complex SQL statement although I do have to admit I've seen some multi-hundred-line statements that gave me the shivers - mind you, these were from customers who had no control over the schema: a DBA would have first re-engineered the schema to avoid such a monstrosity.

But, as long as you're happy with the limitations imposed by de-normalisation, you can do whatever you want. It's not as if there's a band of 3NF police roaming the planet looking for violators :-)

The immediate limitations (there may be others) that I can see are:

  • You'll be limited (initially, without a schema change) to five phone numbers per location. From your description, it doesn't appear you see this as a problem.
  • You'll waste space storing data that doesn't have to be there. In other words, every row uses space for five numbers regardless of what they actually have, although this impact is probably minimal (e.g., if they're varchar and nullable).
  • Your queries to look up a phone number will be complicated since you'll have to check five different columns. Whether that's one of your use cases, I don't know, so it may be irrelevant.

You should probably choose one way or the other though (I'm not sure if that's your intent here). I'd be particularly annoyed if I came across a schema that had phone numbers in both the store table and a separate phone numbers table, especially if they disagreed with each other. Even when I de-normalise, I tend to use insert/update triggers to ensure data consistency is maintained.

Upvotes: 7

Related Questions