By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,995 Members | 1,195 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,995 IT Pros & Developers. It's quick & easy.

A Normalisation problem

P: 4

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

Sep 12 '08 #1
Share this Question
Share on Google+
1 Reply

P: 310
I do not see why you would want to combine services and items to one table, and service_providers and service_customers 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_customer is not a service_provider. 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_providers and service_customers 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.
Sep 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.