Using lookup tables in database design

Question

Using lookup tables in database design

Coldin Alaska

SSC Veteran

Points: 290
More actions
April 16, 2007 at 5:51 pm

#370499

There is a controversy going on in my shop. I am trying to design a sqlserver database using one Utility table that lists all the types of utility companies that services the different buildings in our company. I would prefer to use a single utility lookup table with the UtilityID as the primary key and information about the utility company as nonkey fields such as UtilityName, UtilityFax, UtilityPhone, etc. Our buldings table will consist of BuildingID as the primary key, GasCompanyID, ElectricCompanyID, etc. to define which utility companies services the building. Howerver, someone else proposes that I split up the lookup table into different Utility companies such as GasCompanies (pk= GasCompanyID), ElectricCompanies(pk=ElectricCompanyID). This person is concerned that if I use only one utility lookup table, the database design will need multiple instances of the single table. I worry that his design might cause me to create separate data entry forms for each Utility table. Who is correct and what is your valued opinion on this matter. Thanks much for any respones to this post.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

I see no reason why "the database design will need multiple instances of the single table". I assume they mean "multiple physical copies"? Can that person give an example of what they mean?

One of the bad things I see in your design is that you have UtilityFax and UtilityPhone columns... those should be in a separate table so that each utility can be assigned more than one fax and more than one phone without abhorrent denormalization of the Utility table. Same goes with addresses and the like... should be in a separate table...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Antares686 SSC Guru Points: 125444 More actions · Answer 2

OK I will take it a bit further and suggest they are wrong and you are right but you might want to carry a bit further.

But the reason I say this is you haven't taken far enough to ensure data intergrity of your entities.

Currently I use the same company for my Internet, Cable and could also for Phone service. I have also had a provider in the past (rural community) who handled both eletric and natural gas. And at my current address I get water from one company and sewage from another (but could be both from the same).

I would approach this way

Providers

Provider_ID (Primary Key ID)

Provider

(Provider info)

Utilities

Utility_Service_ID (Primary Key ID)

Utility_Service (This will be things like Cable, Sewage, Water, Telephone, 800 Line, etc)

Utility_Providers

Utility_Provider_ID (Primary Key ID) (Might choose to make composite primary key on the other two column and forego this column, up to you)

Provider_ID (FK to Providers)

Utility_Service_ID (FK to Utilities)

Buildings

Building_ID (Primary Key ID)

(Build info)

Building_Utilities

Building_ID (FK To Buildings)

Utility_Provider_ID (FK TO Utility_Providers)

Account #

(Primary Key on Building_ID, Utility_Provider_ID)

Now for the drops you mostly are using the Utility_Providers and Utilities to control those. But you companies are protected to ensure you can keep referential integrity up. The other persons design creates points of failure referential and yours din't seem to take into account utilities provided by the same provider.

Of course this was off the cuff and still requires tweaking for your needs specifically and normalization.

Coldin Alaska SSC Veteran Points: 290 More actions · Answer 3

Thanks for taking it a bit further and I very much like your idea of a joint Building_Utilities table.

I have never thought that there are Utility companies that can provide more than one type of utility service. I am not sure that this is possible in our thinly populated state of Alaska, but I will check.

Thanks again.