I considered creating a separate value table for every type in use. (like "integervalue", "doublevalue", varcharvalue" etc.) This leads to the problem, that I cannot refer to these value tables with one foreign key.
One possibility would be to store the name of the table holding the value of the property in the property-table.
But that would mean that I store meta data within the data, which is rather ugly from an academic standpoint. It also means that I would have to perform two separate queries, one to obtain the table-name, the next to obtain the value from the table with that table-name. Or is there a way to dynamically specify the table to use in the from clause?
SELECT * FROM property p, TABLE_whose_name_is_that_of(p.tablename) AS valuetable
Another possibility would be to create one value-table only, with a blob-column that holds the value in its binary representation, and a column that specifies how to interprete that binary data. Not too nice either.
Re: a challenge for DB-design specialists: Simulating Polymorphism in the database
I would not use a BLOB column. Why not just use a VARCHAR column of sufficient length to hold the longest values, e.g. 30 chars? (The longest values should not be very big anyway, since these are foreign key columns).
Whatever you do it will not be pretty, and it does involve storing meta data. Here is a possible solution:
CREATE TABLE property_type
( pt_code VARCHAR2(10) PRIMARY KEY
, pt_description VARCHAR2(100) NOT NULL
, pt_datatype VARCHAR2(1) NOT NULL
, pt_data_length NUMBER NOT NU::
This will define your property types like "length", "interior diameter". For example:
pt_code = 'LENGTH'
pt_description = 'Length of pipe in metres'
pt_datatype = 'N' /* Numeric */
pt_data_length = 10 /* i.e. max 10 digits */
You may want further attributes like decimal places, format mask, value requires description y/n etc.
CREATE TABLE property
( ... /* whetever */
, pt_code VARCHAR2(10) NOT NULL
, value VARCHAR2(30) NOT NULL
, FOREIGN KEY (pt_code, value) REFERENCES property_value
Note that you NEED 2 columns for the foreign key because you must identify (a) the value and (b) what type of value it is.
Now you have a database design that is as flexible as you like. The downside is of course that there are no SEMANTICS. You are free to record anything you like about anything you like, e.g. you can assign an internal diameter to a person or a pencil! So maybe now you need to go further with your metadata and define tables to specify what property types are valid/mandatory for what object types...
Thank you for your elaborate answer.
This was one of the possible solutions I considered.
The other one I suggested (separate value tables for every possible type) seems promissing for the more efficient storage of primitive types.
I discussed both variants with a colleague, who preferred the later approach (having done it that way in another project).
He also proposed another idea which would dynamically create tables and subtables for the items with the appropriate number, name and type of properties involved. Although it may seem quite logical, I think that fidling with the schema at runtime might be rather tricky.