how far to normalize these tables

[WebDevGuy]

I asked a question last week about normalizing and that brings up another point, do I even NEED to normalize the tables further.

The tables include user, building, client. (I am using MySQL).

Each of those tables has the same address components - address1, address 2, city, state, and zip.

My thought is in order to follow normalizing standards, I need to have an address table with the following fields; address_ID, address1, address 2, city, state, and zip. Then user, building, client would have a field address_ID.

And the tables, user, building, client, would each have an address_ID.

I am trying to balance keeping the PHP code simple for editing the tables and the need to normalizem the tables.

Your thoughts are MUCH appreciated!

[ApeWare]

I responded to your post at: http://www.sitepoint.com/forums/showthread.php?t=219787

It is still unclear if you will have the need to store multiple addresses for the same entity. If your answer is yes, then you should break address into a seperate table. If the answer is no, then there is no need to break address into a seperate table since the address is specific to the entity and is defined by the entity.
You will not have update or deletion anomolies by storing the address in each of the entity tables (if you will only be storing a single address for each entity).

[WebDevGuy]

the answer is no - so I will keep addresses in each table.

Another question:
I have a table with mulitple kinds of services. I could roll them out to a different table. For example, a consultant provides different services:

Consultant table has fields, c_ID, Name,... service_HelpDesk, service_Database, service_Programming...

For NOW, there is a defined list of services but you never know what the future holds.

Should I break the services out and have them in a different table and also add a JOIN table like:

CONSULTANT table with fields; c_ID, Name,...
SERVICE table with fields; service_ID, service_Name
CONSULTANT_SERVICE table with fields; c_ID, service_ID

?

If so, this really complicates coding the web pages but I'm sure it can be done and it ultimately adds falxibility for future changes.

But it REALLY COMPLICATES coding...

Did I mention it REALLY COMPLICATES coding?

Any ideas and your thoughts are appreciated!

[ApeWare]

A consultant can provide one or more services to a "client" so you could (and should) break it into a different table. You could then specify things like cost in the service and consultant_service table so if the price changes you will be able to store the cost associated with the service, AT THE TIME OF SERVICE. This will also enable you to generate several reports with ease.

If you don't need to store this history you COULD store the service domain in a drop down/value list so it could be changed programatically in one place, but this decision would be based on your business rules, reports you may want to generate, and future growth/flexibility of your application. Without more details it would be wrong of me to say the latter option would be a good idea, but I can say that breaking it out into another table would give you the most flexibility going forward.

[WebDevGuy]

In the above example, I would like to store the history of price changes. In the above example:

CONSULTANT table with fields; c_ID, Name,...
SERVICE table with fields; service_ID, service_Name
CONSULTANT_SERVICE table with fields; c_ID, service_ID, FEE, active

I added "FEE" to the CONSULTANT_SERVICE table.

For purpose of illustration, I might have:

CONSULTANT
1 John Brown
2 Sam Smith
3 Larry Byrd

SERVICE
1 Database
2 Computer
3 Programming

CONSULTANT_SERVICE
1 1 105 No
1 2 100 Yes
1 3 95 Yes
2 1 135 Yes
1 1 135 Yes

Notice the last record in CONSULTANT_SERVICE table changes the current billing rate for John Brown for Database work.

Some of my confusion comes in like this;
I need to display ALL the services the company offers in checkboxes and have checked those services Sam Smith can work on. In the above example, Sam Smith can only do Computer type service work. How would I code that?

A more complicated question than that is how to code an edit page to change that?

Would I query and loop through an array of ALL services and available then assign and checked value to those that are checkec in the database then display those on the page? Then after the page is submitted, I don't know what was there befoore to do I cheange ALL services for that consultant to NO and then loop through the the POST variables and change the ones he DID check back to YES?

I don't know if this makes sense or not and maybe it needs to go to a coding forum. I'll post if there also. Thanks for any ideas!

[ApeWare]

This MAY need to move to coding forum, but...

I do not know what you are using for a front end so I will base my answer on PHP.
Barring any special reporting you may need to do, I do not see whay you would NEED a table for consultant_service. From your description, it sounds like consultant_service is a list of services/rates that a consultant CAN perform. If that is the case, then you could say that consultant_service describes the consultant (John can provide computer and programming services). You could create a field in consultant that stores a multidimentional array containing service->rate. Your service table would contain serviceName and defaultRate. On your edit page you could query the service database and check/uncheck based on if the service is in the consultant's service array. Any changes to this form would update the field in consultant.

All of this depends on business rules, reporting, blagh blagh. But this is another way to handle it.

If you want to go with the consultant_service table, I would only store the services a consultant performs, not services they do not perform.. This way, when you create your edit page you will do a Select serviceID from consultantService where consultantID = x. When you display your form simply query the service table to build your checkbox list and compare each service option to your consultantService results to determine if they are checked and what the rate is.

I hope this makes sense. I have handled it both ways in the past and either works fine. Just depends on how you plan on reporting or if reporting is even necessary.

[WebDevGuy]

I don't think reporting will be needed. However, I do want it to remain flexible enough to change service offerrings later without much disruption of the table structures.

This is my first time REALLY hammering out the normalization of a database with more than a few tables. My actual db has 15 tables...so far.

What I have learne so far is that you MUST get the db structure right first. Then everything will fall into place better.

Thanks for your help.

[ApeWare]

You can add services as much as you want with no issue. The only complication I see is if you no longer offer a service down the road you will have to loop through each consultant and remove that service from the array if you store consultant_service in consultant. So, consider the number of consultants and the chance/how often you (your client) will drop services. Dropping services doesn't happen that often, but there are several legitimate reasons this could happen so you need to be prepared in case it does. How much work could this be if it happens?? If you store it in a table you simply remove any occurance of the service and you are done.

[WebDevGuy]

you mean if I have the consultant_service table, I would be better off if I need to change service offerings - right?

[ApeWare]

Correct. If you will be DELETING services from time to time then the consultant_service table is a good idea (still, only store services that a consultant performs, NOT the ones they do not).

If you will NEVER delete services, or it will be very infrequent and you will not have a large number of consultants then you could create a "script" to delete services from the consultant table and still store the services as an array. This can save you some programming overhead and is suggested only for this reason and based on the above conditions.

The PROPER way to normalize the data would be to create the consultant_service table and be done with it. My other suggestion with the array was just a "real world programming shortcut".

Database normalization is very important, but when you get into larger systems sometimes by-the-book normalization is just not practicle due to the amount of code necessary to support it. You must weigh the business rules and budget/time restrictions and make the decision of how far you want to normalize your data.

[r937]

when you get into larger systems sometimes by-the-book normalization is just not practicle due to the amount of code necessary to support it. You must weigh the business rules and budget/time restrictions and make the decision of how far you want to normalize your data.

i'm afraid i'm going to have to disagree strongly with this

denormalized requires more code, normalized requires less

[Defender1]

Not to mention a denormalized design is more likely to have redundant data.