A Normalisation problem

[mlewis]

Hello

I would be most grateful for a little help / discussion on a normalisation / db structure question I have.

I think the issues are pretty well explained graphically on my link (below) but the main question is I'm wondering whether the tables items and services should be in one table with an identifier and whether service providers and service customers should be in one customer table for the purpose of ordering.

View of my design

I'm not sure if I've given enough information even to be able to answer, but would appreciate some feedback

Kind Regards

Mark

[coolsti]

I do not see why you would want to combine services and items to one table, and service_provide rs and service_custome rs to one table. I do not really see how you can.

For both pairs of items, they are distinctly different "nouns" or things in the real world. An item is not a service. A service_custome r is not a service_provide r. So they should be represented in your database by separate tables.

Sometimes you may find it more convenient to combine entities that have a similar structure into one table, where you add another attribute (another column) that tells what type of entity it is. For example, you make a table to hold service_provide rs and service_custome rs together, and have a column named for example "type" which can be an enum('provider' ,'customer') field. But unless you are going to be able to make efficient use of this more compressed design in your select queries, as when you are often selecting from both entity types in the same query, then there is no reason to combine them into one table.

Sometimes it pays to break the normalization rules to make queries more efficient. Let us say you have an entity that has table columns that are all of fixed data sizes (e.g. int, char(10), smallint, etc.) but one column is a large variable data size such as a blob data type. If your application will perform many queries which do not affect or retrieve the blob data type, then it may be more efficient to make two tables, where a second table holds the ID as foreign key to the main first table and the blob attribute. Table searches for updates and selects will run far more efficiently on the first table with fixed record sizes, as opposed to using only one table which then would not have fixed record sizes because of the variable blob field.