Re: Modelling objects with variable number of properties in an RDBMS

In message <1130610162.744817.239070@xxxxxxxxxxxxxxxxxxxxxxxxxxxx>, datapanix@xxxxxxxxx writes 
i've apparently hit a brick wall in my understanding of table
relationships.  Say I have the following:

widgets:

id     widget
----------------------
1      sprocket
2      cog
3      hammer
4      thingamajig

qualifiers:

id     qualifier
----------------------
1      red
2      blue
3      green
4      smooth
5      crunchy
6      sharp

widgets_qualifiers:

widget_id qualifier_id
----------------------
1         1
2         3
2         5
3         2
3         6

That looks straightforward and is a good way to model a limited subset of attributes and values. If your data fits within that limited subset then you can stop there. The problem arises if the data doesn't fit. The important question that hasn't been covered so far is how well defined is the problem? Do you know the complete list of possible attributes and permutations of attributes at design-time?

Do you know for sure and certain that a widget can't be both red and blue? Do you know for sure and certain that no objects can have more than one colour value? Could it be red on one side and turquoise on the other? Is turquoise the same as blue?

If you don't know that you know then you may need to fall back on the Entity Attribute Value database structure, and that's bad news. (Google for that, it's been described here before.) The problem with that is that it defers the data-modelling from design-time to run-time. That's fine if all of your users are data modellers, and a nightmare if they aren't.

If you don't know what the constraints are on the input data then it's impossible for you to implement constraints in your code. That means that the people doing data-entry could add a "red, green, crunch, smooth cog" to your database if they think that's the best description. Because you can't decide in advance that it's not possible you can't prevent them from doing it.

The best solution that I can think of is to allow any permutation of attributes and values. If you trust the people inputting the data then that's all you have to do. If you need to have the data verified then have the new entries flagged as tentative and have someone review them. Someone in the system needs the authority to declare the data kosher.

These are business problems rather than data modelling problems. You need to design the database to fit the business constraints. You may be the best data modeller in the world but if you have undefined data you won't be able to build a single model to hold it.




--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author.

.

Relevant Pages