Database design with dynamic fields

[wheel]

I'm trying to design a database, having some conceptual problems.

Client records can have multiple widget records associated with them. (i.e. perhaps a record of products that the client bought). There are different types of widgets, with different types and numbers of attributes for each widget type. Some of those attributes are relatively easy, like say price or date. Some attributes may need to point to another type of table - like say a state field may need to be a widget field. In which case, the state field value would come from a seperate state table.

In addition, I'd like this to be relatively generic and understandable. I'm expecting to opensource the app, and one thing people will want to do is add more types of widgets.

Is there an obvious design for this?

Here's kind of what I'm thinking of:
client :
- client id field
- product field (list of productid's)

product table
productid (unique, each record is one per client)
producttype-id (identifies which widget type this is)
productfields (comma delimited list of field values for the selected product type)

producttype table
producttype-id
product type (name of product)
productfields (comma delimited list of field names for the selected type.
---------------------
So we might have:
client: wheel
product field: 238, 557, 10046

producttype-id: 1
product type: Red widgets
prductfields: state,price,dateofpurchase, size,options

productid 238
producttype-id: 1
productfields ohio, $45.00, 12/20/08, XXL,training-wheels

productid record 557 might contain info on a product of type 3 or 8 or whatever.

And instead of Ohio, we'd have the stateid for ohio from the state table, and instead of XXL and training wheels, we'd have the associated id from the size and options tables.

That's not quite perfect yet - I need some additional tables.....but it seems like there's a more elegant solution than the road I'm going down. Is there?

[brotherhood of LAN]

Some attributes may need to point to another type of table

The MySQL SET type [dev.mysql.com] is useful for small lists (64 or less) which you might find handy when preferring one table over more tables. It is basically a BIGINT field (8 bytes) where bitwise operators can be used to determine which elements in the SET are chosen. They're also useful for packing 'flag-like' bits of information that you'd otherwise have to use separate columns for.

client :
- client id field
- product field (list of productid's)

Since it's a one to many relationship then you'll definitely find it easier having an index of clientid/productid in the long run.

In general I think you'd want to avoid having lists in text fields as they can become a pain to update.

[wheel]

OK, you're giving me an ice cream headache :). However, I think you've givem me the solution - thank you very much. I'll point our developer over here.

[ZydoSEO]

I agree with brotherhood of LAN in that I would never use a single field (like client.product or producttype.productfields) to represent multiple values as a comma delimited list. That absolutely screams that you need another table. It will lead to all types of issues as you begin trying to update, insert, and delete from the comma delimited lists. Much easier just to create another table to represent the 1-to-many relationship.

I'm trying to understand what you're attempting to do, but it's not clear. It almost sounds to me like you want a schema that will support defining complex (multi-field) user defined data types (similar to a class in OOP but without behavior) and storing instances of those things described by the data types (similar to an instance of an object in OOP).

Are you planning to store multiple red widgets for a single client? I think I understand that a client might also have several different widgets (item types) defined, each with possibly a different number of attributes/fields and of varying types (integer, string, floating point, currency, datetime, lookup table, etc). Once an item type is defined can it be used by multiple clients or only the one it was originally created for?

[wheel]

I'm trying to understand what you're attempting to do, but it's not clear. It almost sounds to me like you want a schema that will support defining complex (multi-field) user defined data types (similar to a class in OOP but without behavior) and storing instances of those things described by the data types (similar to an instance of an object in OOP).

Well, yes, that's what I want :).

Yes, there will be multiple red widgets for a single client. Both of your last two paragraphs are bang on.

I had dinner with our developer this evening and briefly discussed this with him, and his reaction was identical to yours - (i.e., I'm going down a nasty, ugly path :) ). I knew I was doing it wrong....

[ZydoSEO]

It can be done. It just requires some serious thought. Most CMSs have similar functionality where you can define new item types and then create instances of those items to be used by components/modules and templates.

I've been working on something that might get you started, but it's pretty long. I'll try to post something on it in a bit.