> I have jsut been talking to some developers here and tried to explain what
> sub entities are and how they are used within a relational database. Does
> anyone have any examples of sub entities being used in real life and how
> they were built? In particular how you define the exclusivity of the
> sub-entities.
Steve,
Well, I'm not sure if you mean the same thing by sub-entity as me or not.
Let's see.
In the OO world, "everything is an object". Objects are organised in
two sorts of hierarchies: Containment (" X has a Y ") and Inheritance
("X is a Y").
In entity-relationship modelling/logical data modelling, you define
Entities, which are very similar, if not identical, to the OO concept
of Objects.
Thus in an investment app you might define Entities such as Client,
Portfolio, Instrument, Issuer.
The OO concept of containment is handled to perfection by relational
databases. Elementary data goes in columns. Repeating data goes in a
"detail" table accessed via a one-to-many relationship. A client account
has many portfolios:
---------
| |
| Client |
| |
---------
|
|
/\
---------
| |
| Pfolio |
| |
---------
Now, how do you do support the classification-type things which OO
supports with the concept of Inheritance? For example, an Instrument
could be an EquityInstrument or a DebtInstrument. In turn, a
DebtInstrument could be a StraightBond or a ConvertibleBond etc.
These are sub-entities.
An entity is drawn as a box. A sub-entity is drawn as a box inside
a box.
------------------------------------
| Instrument |
| |
| ---------- ---------- |
| | | | | |
| | EqtyInst | | DebtInst | |
| | | | | |
| ---------- ---------- |
| |
------------------------------------
Frankly, entity modelling doesn't help an awful lot because these
things are quite hard to model in a relational database however
you draw them in a picture. Conceptually, this is a big strength
of OO but unfortunately we all still have to store info in
relational databases...
You can define an InstrumentType table and have an InstrumentType
field in your instrument table. That takes care of the exclusivity
- an instrument can only have one type. But what about all the
attributes?
Obviously your Instrument entity translates into an Instrument
table with columns for all the Instrument entity's attributes.
This is your "base class" in OO terms.
Now you have basically 3 possibilities:
1. Define columns in Instrument for all the attributes of all the
sub-entities. This means that there will be wasted storage space,
and more importantly every time you want to create a new subclass
you have to modify the table. But it's nice and straightforward.
2. Create a table to hold the attributes of each sub-entity. Now
you have an Instrument table, an EquityInstrument table, a
DebtInstrument table and so on. The Instrument table holds the
information common to all types; the DebtInstrument table holds
the information specific to debt instruments. This means that
every time you want all the info on an instrument you have to
join two tables.
3. Same as 2, but get rid of the Instrument table and replicate
its columns in all the sub-entity tables. This means you need to
keep the tables consistent with each other. It also means you have
a slight headache if you want to implement a unique numbering
system across all your Instruments.
I have seen all these approaches used (and others!) Which one you
choose for an application depends on your requirements for speed,
maintainability etc, but above all, functionality. On the face of
it, option 2 has got to be the ideal one, but...
Problem 1. Deeply nested inheritance hierarchies: a US T-note is a
sub-entity of a medium term bond which is a sub-entity of a straight
bond which is a sub-entity of an Instrument. How many tables are
you going to end up with here?
Problem 2. Orthogonal inheritance hierarchies (multiple inheritance):
a US T-note is also a sub-entity of a US government obligation which
is a sub-entity of a risk-free instrument which is a a sub-entity of
an Instrument. What do you want to do, store all the information
again in a different set of tables?
These issues crop up on every project in this application domain.
The simple answer is to use option 1. The more important lesson is
probably that your database shouldn't try too hard to embody the
*structure* of the problem domain, it should concentrate on
storing the *basic facts* correctly and let your application logic
figure out how to make sense of them.
Then again, maybe you were talking about something completely
different :-)
Nick
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
