database design for multiple types of products

[MadDiver]

Guys,

 

I need to create an application that handles several product types/categories. Each product type can have totally different fields to describe it. For instance a car would have Year, Make, Model, and Miles, whereas a house would have Year, Address, SQ Feet, Amenities, etc.

These are just examples but you get my drift.

 

the application needs a form to input, show and search the data for each of the product types.

 

I cannot hard-code each product type and its associated fields in the database, since one of the requirements is that an administrative user can create any product type he/she needs. The application then has to generate the forms for data input, show, and search on the main site.

 

What are your suggestions as how to best address these requirements?

What would be the best DB schema?

 

THANKS!!!

[Buyocat]

You want to be able to generate a table through an admin panel and have that simultaneously generate forms in the view and hook them up with a model?  If that's right then I think you're in over your head because that'd be some serious code generation.  That said I'll name one thing that I think might help in a big way.

 

Look into object relational mapping.  Active Record is one example of ORM (it's a pattern in popular frameworks like ruby on rails and cakephp).  That said ORM won't get you all the way, and I know for a fact that Cake won't either. You may be able to find a good ORM which you can use to connect to a second mapper.  The design would look something like this:

 

Table ---> config file // your admin would edit both from his side (a script would do it on button push, let's say)

config file ---> ORM // the ORM wraps the table based on the config data

ORM ---> View wrapper // the view maps values from the ORM to HTML it might do this using the old or a new config

 

Still not sure how you would set up the various code paths between these things.  However I think it's clear how you could acheive forms being generated solely based on a table's presence in the config file.  I think you would need to build the application around those two relational mappers so that some an incoming request was used (maybe through reflection) to point the maps to the correct table and then let them move data back and forth.

 

I can talk some more about his if you have questions, let me know.

[MadDiver]

Boy that does sound complicated... ???

 

I was thinking I would have  a basic table with my "common" product properties, like title, description, price, date, etc. And some how add the other properties in according to product category or type, maybe from another table.

 

The admin user would create a product type, specify its additional fields/properties, and the data input, output and search forms would be dynamically generated from this definition.

 

 

 

[Buyocat]

That could work, but what do you mean when you say dynamically created?  Would a HTML file be generated as well as a new table in the DB?  If that's the case then it might work, but how would you connect the HTML form to the database?  and the vice versa?  I'm not trying to say it's impossible I haven't thought it out, but you'll need to answer those questions somehow.

 

The only reason I jump to the ORM solution is because I'm just wrapping up a round one with a ORM class and I have dreams of how to use it running through my head.  I do like your idea of having one "master" table.  If nothing else it will make for a more manageable database because you can abstract all the common elements (like title, date_added, date_modified, etc) to a single table.  Just to warn you that will complicate your queries down the road, but it's probably worth the trouble.

[MadDiver]

That could work, but what do you mean when you say dynamically created?  Would a HTML file be generated as well as a new table in the DB?

If that's the case then it might work, but how would you connect the HTML form to the database?  and the vice versa?

 

I was thinking of having the admin user create the product type by specifying what fields are needed, types, names, default values, etc. Then this info would be stored to a forms table that would be used to generate the actual HTML form in the main site.

 

So once a product is entered via the generated form it would be saved to the "master" product table, and the "extra" fields to another table, the extra_fields table. something like

 

tb_produtcs

id | title | description | date | price | ...

 

tb_extrafields

product_id | field_name | field_value

 

The extrafields contain the particular info about a product type specified by the admin user. would this work? or would it be better to store each product type in its own table?

 

 

[Buyocat]

So the extra fields table is something like a hash for miscellaneous information?  That could work but it might be difficult, and I'm not sure how you would be able to query against it in the future.  Maybe the plan is to take an id and find all the rows in the second table with product_id == id?  But what if you wanted to fetch all the cars products?  Would you first collect all the ids with type == car and then all the rows with product_id == those ids?

 

Making separate tables for car, house, etc, which extend off the base table would make more sense (at least to me), but it might be harder for what you're trying to do.  Then again, won't the admin create the table and all it's fields at one time?  And won't that be a relatively infrequent event?  So making the admin operation a more expensive one (for instance creating a table and possibly a few other files on the fly) would be a better trade given that it might translate into better performance for the more common operations such as querying for all car products, etc.

 

Just some thoughts

[kathas]

If you choose to do it the way MadDiver said querying the db to get all the products from one type will be absolutely no problem SQL was made for that...

 

You can choose to select results from 2 tables with the condition (example) table1.id=table2.id and it will give all the products including the extra fields....

 

But is that way (creating a new table for each product type) effective???

It depends on how many product types you'll have... Maybe if you have too many it will take up too much disk space...

 

I thought of creating one table that will hold the extra fields for each category

Example:

category

extra_fields

carYear|Make|Model|Miles

 

and another one that will hold the values

category

id

extra_fields

car1321990|NIssan||100

 

and then you can use simple functions to get all the data you want the way you want them...

But i dont know how fast will this be in runtime besides i am just throwing ideas... you should test to see... You could also create a new table for each category and dont worry about extra fields and stuff (this gets us back to the resources problem)...

[MadDiver]

Yes unfortunately we are talking about many different product types, well over 100. Would you still go with the multiple table solution?

 

What concerns me is the fact that I will need 3 forms per product type, Input, Search and Edit/Output, and the search and output modules are going to be under heavy load.

If any optimization needs to be done it would be in the product display and search, since the product will only be added once, and may be later modified a couple of times.

 

Kathas: your table structure would need full text search to get the information out of the extra_fields wouldn't it?

Say you are searching for all 1990 Nissans?

wouldn't this be an expensive operation?

 

THANKS !!!!!!

[kathas]

Sorry for the late reply i dont know if you still follow this thread...

 

Anyway the search would be easy just a simple LIKE

example:

you want all the 1990 Nissans. From the table that says how are the extra fields structured you get that the first  and second are the year and make. Then you structure a query like

SELECT * FROM my_table WHERE category='car' and extra LIKE '1990|Nissan|%'

if it was the first and third

LIKE '1990|%|Nissan|%'

 

I cant tell you which one to use neither can i choose for myself because i dont know exactly what functionality you want to have... But this way you can easily achieve basic functionality or even advanced if you try a bit...

 

The other except more resources needs more programming. It can provide full functionality though. But really 100 tables??? i think they 're a lot...

 

I just threw an idea there i dont know if i would follow it if i was to program this...

[MadDiver]

I found these articles that deal with this "age old" question: Extending the database to suit the customer's needs

 

http://codebetter.com/blogs/jeremy.miller/archive/2007/02/19/How-do-you-extend-and-customize-a-database_3F00_.aspx

http://msdn2.microsoft.com/en-us/library/aa479086.aspx

 

basically they create some extra tables that contain the product type definitions and then map this table to a product sister table that contains the extra field data.

 

I think I'm going to try out this strategy.

THANK YOU very much for your valued input!

[Buyocat]

Good luck and tell us what design considerations you learn from the experience.