CODEWITHSUNDEEP
mysqlsqlforeign-keys
Jeppe
Jeppe

Reputation: 2223

Correct MySQL database layout

Creating a custom CRM system i ran into a wall figuring out the correct way for MySQL layout. The system has to be dynamic, ie. expandable for multiple new products over time. Each product should have individual billing and each contact should have multiple products.

Should i use tables like this:

Contacts{ ID }
Product_1{ ID; FK_ID; +Custom columns}
Product_2{ ID; FK_ID; +Custom columns}
Product_3{ ID; FK_ID; +Custom columns}
+ 15 more products (Expanding)

Or should i use something like this:

Contacts{ ID }
Products{ ID; FK_ID; +100 other columns (expanding)}

The first example will be easy to configure, but will contain a lot of tables over time. Also binding contacts to products will need another table.

The last example will be easier configuring in PHP, but will be a mess looking at the table.

Any other ideas? Which is faster of the two? Which is most "common"?

I get the feeling that none of these solutions are optimal, there has to be a better way?

Upvotes: 0

Views: 85

Answers (2)

acfrancis
acfrancis

Reputation: 3681

If your products are really that different from one another, create a separate table for each one:

Product_1{ Product_ID (PK); +Custom columns}
Product_2{ Product_ID (PK); +Custom columns}
...

Now, take it a step further with these tables:

Product {ID (PK auto increment); Contact_ID (FK), Type_ID (FK)}
Product_Type {Type_ID (PK); Type_Name}

Insert as many rows in Product_Type as you have custom product tables (Product_1, Product_2, etc). Everytime you create a new specific product table, add a row to the Product_Type table.

Also make the Product_n.Product_ID a foreign key (and NOT auto increment) to Product.ID.

This way, you can refer to any type of product generically via the Product table or specifically via the correct Product_n table. You can even tell what type of product it is just by looking at Product.Type_ID.

If you want to make it completely bulletproof, you can add the Type_ID column to each Product_n table, include it in the PK (along with Product_n.Product_ID) and add a check constraint such that Product_1.Type_ID == 1, Product_2.Type_ID == 2 and so. That will ensure that you can't screw up the relationship between Product and Product_n even if you try but, to be honest, I think it's overkill and I haven't had problems with the "non-bulletproof" version.

Upvotes: 1

trf
trf

Reputation: 1394

You could consider introducing a third table to store the dynamic "features":

Contacts{ Id; }
Products{ Id; }
ProductFeature{ Id; ProductId; FeatureName, FeatureValue }

This does make it more complicated to query, but has the advantage of not requiring a schema change for each new product or feature

Upvotes: 1

Related Questions