Good design?

Hi!

I want to add "inheritance" to my database. I have of table X witch attributes a1, a2, a3, a4, a5, a6 and second table Y with attributes a1, a2, a3, a7.

I want to create additional table Z with attributes 1a, a2, a3, key and tables X: key, a4, a5, a6 and table Y: key, a7.

I have more of those sub-class. Set of common attributes is about 5-10. Additional attributes range from 1 to 20 per table.

We have also large dynamics of our database. Often (1 per month) we have to add about 2-5 attributes to some subclass (or remove some attributes).

It's good idea for "attributes inheritance"?

I know those disadvantages:

to insert (update, delete) record from X, I have to insert record to Z and X

I found solution that told me to add all attributes to one table. It's not good for me. Storage space is not optimal. I don't want table with 100-200 columns in majority filled with null's.

Any better ideas?

Best Regards,
Walter

>>I found solution that told me to add all attributes to one table. It's not good for me. Storage space is not optimal. I don't want table with 100-200 columns in majority filled with null's.<<

Storage wouldn't be a problem, as pretty much the same amount of space is taken up. But it is really ugly.

>>It's good idea for "attributes inheritance"?<<

It really and truly depends. If you really have a supertype that is useful to have that has the attributes that are the same AND the supertype models the same thing. Like an vehicle for tracking, then a car and a motorcycle because they have different attributes that the client needs, it is a great thing.

If you just have disparate things being modeled that happen to have some of the same attributes (like most tables I create have a name column, and update date, etc) then it is a bad idea and not necessary.

>>We have also large dynamics of our database. Often (1 per month) we have to add about 2-5 attributes to some subclass (or remove some attributes).<<

This sounds like a bad idea in principle, but it may not be. But when people say they add new columns too often it raises the red flag that says that the problem isn't quite understood. Most relational databses should be quite static with the data they store if the problem that is being modeled is understood. But there are obvious exceptions, and yours might be it :)

|||

Hi!

Yes you've right, there is not problem in storage, but 50-70 columns per table is not good ...

In fact I design database of bialiffs, offices, courts, etc. for large lawyer's office. Those institutions have many common attributes like phone, name, etc. Altough some of them have more or less additional parameters. Next... offices divides on tax offices, registry offices, province offices, an so on... But they all are institutions.

In previous design we have unique id for bailiffs, courts and offices. All kinds of offfices was in same table. The problem arose, when we have to build list of debt collectors in our other soft... Naturally it is bailiffID. But some of tax offices can be debt collectors also. So we have to store not only ID, but context also (tax office, bailiff, etc.). In new design we have to point only on InstitutionID.

Our system must be dynamic! :) Yes, we can describe every institution, but our friendly lawyer's office doesn't need it! They don't need 200 attributes of tax office, but only 10-15 witch are necessary. Our often changes follows on:

* changes in our Polish law - unfortunately system is not stable today ... :( But isn't bad ... :)
* In next iteration of our project we transtorms next "pencil and paper" procesess into software. We must be "agile" :)

Best Regards,
Walter

|||

>>Yes you've right, there is not problem in storage, but 50-70 columns per table is not good ...<<

Taken out of context, I wouldn't agree, but if they don't all need the columns then I wholeheartedly agree.

>> previous design we have unique id for bailiffs, courts and offices. All kinds of offfices was in same table. The problem arose, when we have to build list of debt collectors in our other soft... Naturally it is bailiffID. But some of tax offices can be debt collectors also. So we have to store not only ID, but context also (tax office, bailiff, etc.). In new design we have to point only on InstitutionID.<<

I like the idea. This is a fantastic use of a subtype, to be sure.

>>Our system must be dynamic! :) Yes, we can describe every institution, but our friendly lawyer's office doesn't need it! They don't need 200 attributes of tax office, but only 10-15 witch are necessary. Our often changes follows on:<<

I fear the word dynamic because it tends to mean overly flexible. Flexibility is for the UI guys. Data that is important enough to design a database for is important enough to design right (not that you have said anything that leads me to believe any other way,) and protect. I will trade a bit of hard work and complexity any day for a overly flexible, too hard to manage system tomorrow. I work hard now so "future me" can cruise.

>>* changes in our Polish law - unfortunately system is not stable today ... :( But isn't bad ... :)
* In next iteration of our project we transtorms next "pencil and paper" procesess into software. We must be "agile" :)<<

Don't confuse agile with overly flexible because no one will confuse overly flexible with failure. Agile is good, but build the foundation of your systems on solid rock and the rest will come easy. :)