Table structure

Hi,

The architecture you described does not fall into the common relational or

object-oriented model if you want to use 1 table. And in fact, I would recomend

that you review your design because it is very likely that something is really

wrong with it. Here are some ideas for you to consider.

If I get it right, the problem behind the problem is that you want to store

employee properties which structure is dynamic, i.e. may be different for

different employees.

1. Analyze whether all your employees fall into a limited set of "classes". If

yes, you can design a table per each class and link them to some "master" table.

The details can be found here: http://www.chimu.com/publications/objectRelational

2.If there are no stable set of classes OR you want additional flexibility OR

employees can "change" their classes dynamically, consider storing properties of

employees in a separate table. You'll need one master table, which stores employee
ID's and those properties that are guaranteed to be common to all employees (e.g.

Name). Then add another table called "employee_properties" with the structure like

this:

create table employee_properties (
employee_id INT NOT NULL REFERENCES employee,
property INT NOT NULL,
value VARCHAR(255),
PRIMARY KEY (employee_id, value)
)

the fact that an employee with id I has a property with id P and with value V is

represented by presence of the following record in the employee_properties table:

(I, P, V).

To query employees you'll need to use an extra join per each property you

interrogate or display:

SELECT p1.value salary
FROM
employee e, -- employees
employee_properties p1, -- "salary" property, id 111
employee_properties p2 -- "age" property, id 222
WHERE p1.employee_id=e.id AND p1.property=111
AND p2.employee_id=e.id AND p2.property=222 AND p2.value > 35

(continued)

3. Variation of 1. Look for DBMS called Cache' (www.e-dbms.com), it supports

inheritance of tables.

4. Variation of 1. You may decide to dynamically manage table structure by means

of create/drop table statements.

5. Youmight use the approach commonly found in Lotus Notes. The employee objects

should be stored in database using some simple persister. E.g. "Jisp" would be

just fine if it is Java that you use. These persisters just convet an object into

abyte-stream and store it on disk. You access objects by there ids. To access

object by properties (e.g. all employees with age > 35) you'll need to create an

index. Jisp allows creating indexes. Also you can use full-text indexing engine to

create just one index to handle all the properties. Look at Lucene, it does that.

The disadvantage of #1, #3 and #4 is poor dynamicity. Advantage is speed and

ability to use complex SQL queries. #2 allows SQL, increases in dynamicity, but

can have poor performance on complex queries. #5 is great both in performance and

dynamicity, but is lacks automated support for complex queries (you'll have to

traverse the index yourself) and also you might lack the capabilities of an

enterprise DB such as transactions, recovery, concurency, etc.

Well finally, you can combine #2 and #5 to improvee the retrieving speed of 2.

Store serialized objects as blobs, but also index the fields you want to search by

means of "properties" table.

There is abosolutely NO WAY you could do such a thing. It defeats the purpose of databases. If there are certain fields that are not applicable to some of the rows, thats ok, you can still leave them blank. Simply don't enter anything. I know you are probably trying to avoid resource waste, but my advice to you would be FORGET about it, you will never find such a thing.

This question was auto closed due to inactivity