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.

what you are describing as the "features" table is the "one true lookup table"

it's not a good design, no

see OLAP design with Key Value Pairs

Thanks for the really quick reply!

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?

the only column name i would not use is one which repeats the name of the table in the column name

but that's just me

So just to clarify, in a table named "shapes" you wouldn't use "shapename" or "shape" as column names but you would use "name"?

Are there any negative implications to having these twenty tables all with a column named "name"? Should I try to be more creative with the column names?

Thanks again for your quick replies.

the only one that comes to mind is that if you use more than one of these columns in the same query, you will want to assign column aliases

I think I can live with that.

Thanks for your time and help. Much appreciated.