I wanted to discuss some database design issues so I typed "database forums" into Google and "I'm Feeling Lucky" and here I am, so I am hoping there might be someone here who will indulge me.
I'm converting an old web application from using Perl and text files to use PHP and MySQL. There is a form in the application which requires a number of features of an object to be selected.
So by way of example an object is specified, after which its color could be defined as either red, green or blue. Then its shape could be defined as circle, triangle or square. Then its material as metal, wood or plastic. There are some twenty odd of these features to specify options for.
Now I was planning on having a table for each feature ie. a colors table, a shapes table and a materials table (each with a color, shape and material column - is that an acceptable naming convention?)
There are twenty different features so it seems like a lot of tables and also a lot of foreign keys in the "objects" table. Is that a sign of bad design? How many tables, all relating just to the same main table, is too many?
Would it be be better to have a "features" table with a column for "type". I guess "type" would have to be an id relating to a "featuretypes" table? This would be a problem for features with different data types though wouldn't it?
What are the implications of either of these options in terms of design, efficiency and future development of the application? Is there a completely different, better design solution? I particularly want to make it easy to add new object features and maintain current ones. A new feature of an object might possibly be any data type: varchar, int or boolean for example.
There are some useful links there with a lot of information to digest. Both the One True Lookup Table (OTLT) and the Entity-Attribute-Value (EAV) models are options I had considered without knowing that they were common ideas (common enough to have names!). I will resist the temptation to use either of them as I can see they could both lead to trouble.
I guess I will stick to the twenty separate tables unless there is another model you could suggest?
With these tables what naming convention is most acceptable? For example with a "shapes" table should the column name be "shapename", "shape", "name" or something else?