db design & column datatypes

Hello. Thanks for taking time to look this over. I haven't been able to find a example to use as a guide.

The scenario involves products and their options. I have simplified this example to make relationships one-to-many. The Product to Option table relationship is basic:

My problem is with the fields for the Option table. Not all options will have a value for each attribute. In theory, no problem. Use a NULL. The problem arises in that this table would be very wide, and possible have to accomodate new columns being added

So I tried creating a third table, Option_Attributes. I like the concept because I can add new rows instead of columns.


But how do i handle the different datatypes each attribute can have? The fourth column would hold a value. If the attribute in row 1 was "name" the datatype of the value column would be varchar. But what about if the 2nd row was price? I would prefer to use a decimal datatype so I can perform calculations in SQL statements.

Any suggestions would be appreciated.

Thanks

This is one of those somewhat troublesome areas of relational database design.

The usual "don't bother me with theory" approach is just to live with the nulls. In your case I think this would be sloppy, though, because this sounds like a fairly malleable list of attributes. You will end up with nulls littering your table and querying can be a pain.

Then there are those who would say just have a two-column table for every new attribute you think of (one foreign key column, and one attribute value column). In some ways this is the most "airtight" and theoretically sound approach. (See an interesting presentation by Hugh Darwen (PDF document)). At least this approach allows you to add or take away attributes at will without interfering with anything else,

But of course the problem with this is that if you have a lot of these small tables your queries can get cumbersome. But, with the right combination of views, this might not be too much trouble. This depends to a certain extent on the DBMS you are using. (MySQL doesn't have views, for example)

You might have a decent middle ground with your Option_Attributes table, except that tables in SQL don't allow the datatype to be different on each row of a column. But, with the right typecasting in your queries, this can be done dynamically while querying. Again, this depends on the feature level of your DBMS to some extent. Also, you might consider stored procedures for handing the querying logic.

So, what DBMS are you using?

I am actually facing this situation with two jobs: one in MySQL/PHP, the other is more R&D and is currently in FileMaker Pro 6. The more difficult of the two is the FileMaker Pro project, so I'll give some specifics on that.

I want to create a db that contains product info for parts from different manufacturers. There is a general structure to the data. There are manufacturers that each make many parts. Each part is made by one manufacturer. Parts have attributes. But part X has "type," "stock no.," and "price" as attributes. Part Z has "W x H x D, " "weight," "stock no.," and "price" as attributes. Part A has "W," "H, " "Max Bundle Diam," and "price." You get the picture.

I guess I am trying to see if I can "dynamically" create attributes or new tables, I am not sure of the direction.

Ultimately, this data needs to be exported to a text file that will be imported into Quark XPress for page layout. I mention that because that adds another process where typecasting or some other process might be able to be used.

Thanks for your time and help.

RE: the PDF you mentioned. Didn't realize Codd died so recently.

So, what you are saying is you have different "types" of products, correct? You have certain attributes that all products have in common, but each type of product has a different set of attributes? If this is the case and if you have a set number of types, you could use subtypes. In this case, you would have one prodcut table in which you have the attrbiutes that all products share and a table for each product type. You have a product_id as the primary key for the products table. In each of the subtype tables you will use this same product_id as both the primary key and as a foreign key pointing back to the main products table.

Another alternative is to create a field called Other_Product_Info, or something like that.

This field would contain non-common info. Things like price would still be in the price column in the table.

The trick is that the Other_Product_Info column would store the misc/other product info. as an XML string which could be parsed when you pull up the product on a web page.

Of course, with that, your're getting into XML parsing, and it would be more difficult to search, sort, etc on those attributes, as there would not be a unique field for each.

Just a little airy-fairy brainstorming. I'd be curious to hear if others have tried this approach when faced with the possibility of dealing with an ever-expanding number of attributes.

I think it would be a big hassle managing that data, especially since your putting things into the code that should be done at the database level. I think that would be too denomralized, imho. Also, it takes away the definite structure the database allows you to enforce on your data as you could have any type of data in any xml tag. Using subtypes allows you to enforce the data to be the correct type and the correct attributes to be supplied (you can specify NOT NULL, unique, and other constraints).

Correct

IF there is an actual set number of types it will easily be in the dozens, and could be in the hundreds. Regardless, that means a lot of columns,a lot of nulls, and new columns being added periodically. I am not opposed to that, but I am concerned about performace and integrity of the database.

Actually, my suggestion of using subtypes means that you would have a separate table for each product. There would be one common product table that would have common attributes but you could create special tables for each product type so the table only has the relevant columns. That would allow a lot of control over the data stored for each product type.

I like it!

I see what links the subtype tables to the main products table, but how do I go from the main products table to the subtype table that holds the specific data for a given product? What is in the main products table that points to that specific product's subtype data? Am I searching every subtype table looking for that matching prod_id? I guess that would not be too bad.

That's a good question, one that I'm stupmed at right now. I'm using subtypes in an upcoming project and have been wondering how one would do that. You would have to know the subtype before hand to query efficiently. Perhaps you could add a field to the product so you would know to which subtype it belongs. The only thing is you would have to do two queries, the first time to figure out the subtype, the second time joining the two tables to get whatever data you want. I keep thinking there has to be a better way, but I haven't thought of it yet.

Well, I'll do a little more testing and searching and if I come up with a good solution, I'll place it here.

Thanks for the helpl!