Re: Select from a different table based upon the name in a field?

[Erik Petersen]

Store your structures as generic (opaque) data. Classes in one table, objects in another, attributes in another. Link the attributes to objects, objects to classes. You can have any number of classes, objects and attributes this way. You can breakdown different attribute tables into their own tables if you need to but a single table with null columns will probably suffice. Look at eZPublish 3 (www.ez.no). That's what they do. If you start to depend on table structures you will quickly run into problems.

create table classes (
id as int(8) primary key,
name as varchar(30),
...
);

create table objects (
id as int(8) primary key,
classid as int(8) foreign key,
...
);

create table attributes (
id as int(8) primary key,
classid as int(8) foreign key,
name as varchar(30),
datatype as int(4),
...
);

create table values (
id as int(8) primary key,
attributeid as int(8) foreign key,
objectid as int(8) foreign key,
intval as int(10),
floatval as float(10),
textval as text,
...
);

This method is infinitely flexible and efficient with the downside being that the relations are a critical part of the data.

Hope this helps...