Schema dilemma
Laila Frotjold
I am having problems designing the following situation: We need to
store information regarding different resource objects. The objects
will be of different types, for example Cabincruiser, MTB, F16,
Submarine etc. Each type should have default values for attributes
such as maxSpeed, maxSensorDistance etc, but these values should also
be possible to override for each particular object. The object
instances does also have additional attributes, for example Position
and Owner. So far we have defined the following tables:
OBJECTTYPE(TypeID, Name, MaxSensorDist, MaxSpeed ...etc)
OBJECT(ID, Type, Position, Owner, MaxSensorDist, MaxSpeed ..etc)
Most OBJECT tuples will have the same value as the corresponding
OBJECTTYPE tuple for the columns that have the same name, however,
some will differ. This design smells awfully of duplication though, so
I was hoping that some bright mind out there knows how to model this
in a more elegant way. The main challenge appears to be the
default/overridable problem.
I will be very grateful for any responses!
Best regards,
Laila Frotjold
System Developer
--CELKO--
X3H2 Database Standards Committee) had a meeting in Rapid City, South
Dakota. We had Mount Rushmore and Bjarne Stroustrup as special
attractions. Mr. Stroustrup did his slide show about Bell Labs
inventing C++ and OO programming for us and we got to ask questions.
One of the questions was how we should put OO stuff into SQL. His
answer was that Bells Labs, with all their talent, had tried four
different approaches to this problem and come the conclusion that you
should not do it. OO was great for programming but deadly for data.
I have watched people try to force OO models into SQL and it falls
apart in about a year. Every typo becomes a new attribute or class,
queries that would have been so easy in a relational model are now
multi-table monster outer joins, redundancy grows at an exponential
rates, constraints are virtually impossible to write so you can kiss
data integrity goodbye, etc.
The best advice is use a relational design in a relatioanl database,
which means one table per each different kind of entity. You jight
also want to read up on data modeling so you will not write absurd data
element names like "type_id" or use vague magical general purpose "id'
columns.
Kenneth Downs
You are not duplicating information. It is perfectly acceptable to have
default level values at one table, and then to override them at another
level.
What you have left is to decide how to store info in the OBJECTS table. One
thing you can do is copy values into OBJECTS from OBJECTTYPES when a row is
inserted into OBJECTS. This assumes that the values are established at
INSERT and do not change. IOW, a later change in the values of OBJECTTYPE
will not propogate to existing rows in OBJECTS.
The other path is to leave NULL values in OBJECTS for those columns that do
not override the default. Then you make a view that has some COALESCE
functions to resolve each value when querying.
--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec)ure(Dat)a(.com)
Bill Karwin
It's only duplication if the data are _always_ the same in value and
quantity. In this case, your OBJECTTYPE table serves a similar purpose
as a lookup table, in that it serves as a static reference from which
the records in the OBJECT table get their values. It's legitimate to
store these in the OBJECTTYPE table. Otherwise, the defaults would have
to be stored elsewhere, such as in your application code.
There's another characteristic of copying the value into the OBJECT
table even if it's the default: if you choose to change one of the
default values for an attribute of a given OBJECTTYPE, you can do so and
let the existing OBJECT records of that type keep their old values. You
might want the new default to apply, in which case you can UPDATE the
records matching the old default value. But if you don't store the
attribute value in the OBJECT table, you won't have a convenient option
of keeping the previous value; it'll get "updated" whether you want to
or not.
But if you really can't store a value for MaxSpeed per OBJECT record
that is the same value that is in the OBJECTTYPE table, try this:
CREATE TABLE OBJECTATTRIBUTE (
ObjectId INTEGER REFERENCES OBJECT(ID),
AttributeName VARCHAR(100),
AttributeValue VARCHAR(100)
);
Drop the columns from OBJECT that have defaults in OBJECTTYPE (e.g.
MaxSensorDist, MaxSpeed).
Now you can query your OBJECTs as follows:
SELECT o.*, COALESCE(atMaxSpeed.AttributeValue, t.MaxSpeed) AS MaxSpeed
FROM OBJECT AS o
INNER JOIN OBJECTTYPE AS t ON o.Type = t.TypeID
LEFT OUTER JOIN OBJECTATTRIBUTE AS atMaxSpeed ON atMaxSpeed.ObjectId
= o.ID AND atMaxSpeed.AttributeName = 'MaxSpeed';
This lets you store a custom value for MaxSpeed if you want to, by
storing a record in the OBJECTATTRIBUTE table. Otherwise if such a
record does not exist, it'll COALESCE to the default value stored in the
OBJECTTYPE table for the corresponding attribute. If you change the
default and want to retain the previous value, you can do so by entering
new rows into the OBJECTATTRIBUTE table.
However, this type of query doesn't scale well, since you'd need to do
another OUTER JOIN for each defaulted attribute. Most RDBMS's have
practical limits to the number of joins you can do in one query, and I
assume you have more than two defaulted attributes to track.
Regards,
Bill K.
--CELKO--
schema?
Names like "type_id" make a relation a person sick; an identifier
belongs to one and only one entity while a type is a code of some kind
that holds a value for an attribute that can belong to many different
entities (vehicle_type IN ('SUV', 'Sedan', etc.) . Clearly in an
RDBMS, a data element cannot be both **by definition** . Then you
start givign the same data element multiple names, etc.
I am not just being my usual nasty self. I tracked down EAV design to
an original and I wonder if I can track down this fallacy to someone in
the OO world. It is such a fundamentally wrong kludge.
Bill Karwin
> Where did you get the idea that you should mix data and metadata in the
> schema?
Well, one idea is that when someone asks a question, the answer should
work with the design of their current schema, even if it is flawed.
The original poster should avoid the kludgy and wrong solution I
described. I posted it in hopes that it would show a way to avoid the
perceived duplication of data that makes things _less_ elegant in this
case, not more elegant.
Regards,
Bill K.
Laila Frotjold
needed. I will continue the design with the lookup table, clear
conscience now that I know it's acceptable. However, I will try to get
rid of the bad element names in order to prevent future developers
from becoming sick when working with the database. :-)
Anyway, making one relation for each type, like CELKO suggested
(unless I misunderstood) in
http://groups.google.no/groups?hl=no&lr=&selm=1119290053.540612.163880%40f14g2000cwb.googlegroups.com
really would not work well for us, because we need to be able to
create new types quite frequently.
Again, thanks all!
Best regards,
Laila Frotjold
--CELKO--
If you cannot get to a stable schema, then you need something other
than a relational database. You are living in a "Philip K. Dick" world
in which there is no data integrity possible and elephants drop out of
the sky. I repair these attempts at dynamic OO for part of my living;
they usually fall part in a year of production work.
Gene Wirchenko
wrote:
[snip]
> I am not just being my usual nasty self. I tracked down EAV design to
>an original and I wonder if I can track down this fallacy to someone in
>the OO world. It is such a fundamentally wrong kludge.
I am very interested in the story here. While I am on your knee,
Grandpa, what about that case you have mentioned about medicine and
not knowing normalisation and a possible resultant loss of life?
Sincerely,
Gene Wirchenko
--CELKO--
A charity that ships medical supplies to Africa has a volunteer do a
database for them. They can get the supplies broekn down into smaller
than normal units for shipping. So instead of a minimum shipment of a
25 unit box of antibotics that a regular coimmercial buyer would have
to order, the supplier will break them out into the smaller 5-unit
boxes and some extra packing to see that they do not break or spoil on
the way to a war zone. This is a lot of work for the suppliers, but it
is a charity so they eat the extra cost, insane deadlines and
bullet-proof wraping paper required.
The kid that did the database put all of the available quantities in
one column as a comma separated list, like ('05,10,25;50') in the
database. His front end then displayed that same list and used a
substring to fill in the order quantity as a string. So we have a 1NF
violation and the wrong datatype.
The charity now wants to get the suppliers to break things down even
further to single units, so they can make up small field emergency kits
in the US. These kits can be distributed faster. It is more likely
that some of the kits will survive transit and get to their
destinations, so a lose of one order in transit will not be as bad.
The quantity column in the database was changed to ('01,05,10,25;50')
but the front end stays the same. SUBSTRING (qty, 1, 2) is now '01' in
the back end and '05' in the display in the front end. The result was
a clinic full of sick kids, many units short of critical medications
and not able to run down to the corner drug store to re-fill the
medicine cabinet.
Another example of a bad medical database was in an Atlanta hospital.
It would drop orders. The first month or so of operation the
administration loved it. You can save a lot of money billing people
for drugs and treatments they do not get. Medical personnel caught
this one pretty fast and the system was dropped at the cost of a few
million dollars.
I did not get called in on it, so I have no idea what the problem was.
My wife worked there for 13 years.
Gene Wirchenko
wrote:
>>> .. case you have mentioned about medicine and not knowing normalisation and a possible resultant loss of life? <<
[snipped stories]
Thank you. I may be teaching this stuff some day, and good
examples are good to have.
Sincerely,
Gene Wirchenko
--CELKO--
Gene Wirchenko
wrote:
>For some more horror stories, go to this link:
>
>http://www.baselinemag.com/search_results/0%2C2426%2C%2C00.asp?qry=%22We+did+Nothing+wrong%22&filterapp=&site=17
Yikes! And it is not the first case. Remeber the Therac-25?
Sincerely,
Gene Wirchenko
Neo
One can easily model the data in xrdb (before finalizing RM schema).
Copy and paste the script shown below into app's input box and press
submit button. To view the data, expand tree node labeled F16 or
submarine. Db handles normalization, PKs and FKs to a large extent. The
app, which fits on a floppy, can be downloaded from www.xrdb.com
// Create types and make them item of main directory.
// Note: inst is short for instance.
(CREATE type inst *F16 & dir item it)
(CREATE type inst *submarine & dir item it)
(CREATE type inst *person & dir item it)
(CREATE type inst *agency & dir item it)
// Create types for attributes.
(CREATE type inst *maxSensorDist)
(CREATE type inst *maxSpeed)
(CREATE type inst *location)
// Create verbs to relate things.
(CREATE type inst *defInst)
(CREATE verb inst *owner)
// Create a default F16.
(CREATE F16 defInst *defF16)
(CREATE defF16 maxSensorDist +"100 km")
(CREATE defF16 maxSpeed +"5 km/s")
// Create F16a based on default.
// Add its owner is agency USAF.
(CREATE F16 inst *F16a)
(CREATE F16a maxSensorDist (defF16 maxSensorDist *))
(CREATE F16a maxSpeed (defF16 maxSpeed *))
(CREATE F16a owner (CREATE agency inst *USAF))
(CREATE F16a location +"above DC")
// Create a default submarine.
(CREATE submarine defInst *defSub)
(CREATE defSub maxSensorDist +"1000 km")
(CREATE defSub maxSpeed +"50 m/s")
// Below code creates Sub1 based on default.
// Add owner is person John Smith.
(CREATE submarine inst *sub1)
(CREATE sub1 maxSensorDist (defSub maxSensorDist *))
(CREATE sub1 maxSpeed (defSub maxSpeed *))
(CREATE sub1 owner (CREATE person inst *"john smith"))
(CREATE sub1 location +"bermuda triangle")
// Find the type of thing whose owner is a person.
// Returns submarine.
(SELECT type inst * & * inst (* owner (person inst *)))
Ed Prochak
I certainly do since I have worked on embedded systems and medical equipment
(diagnostic, not theraputic) and learned about it then. Also the names in the
article brought back the memories:
<quote>
The appeals in Panama were filed by a second defendant, MDS of Toronto. MDS
owns Theratronics International Corp., which manufactured the Cobalt-60 machine.
Theratronics and its previous owner, Canada Development Investment Corp., are
also defendants.
</quote>
Surely this is different equipment and the names (Theratronics and Therac-25)
are unrelated??
--
Ed Prochak
running http://www.faqs.org/faqs/running-faq/
netiquette http://www.psg.com/emily.html
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost
Ed Prochak
sounds like a game database. Maybe check some books and references for that
type of application?
(sorry I don't program games so I cannot suggest any good resources)
Ed
Gene Wirchenko
<ed.pr...@magicinterface.com> wrote:
>Gene Wirchenko wrote:
>
>> On 28 Jun 2005 08:12:40 -0700, "--CELKO--" <jcel...@earthlink.net>
>> wrote:
>>>For some more horror stories, go to this link:
>>>
>>>http://www.baselinemag.com/search_results/0%2C2426%2C%2C00.asp?qry=%22We+did+Nothing+wrong%22&filterapp=&site=17
>> Yikes! And it is not the first case. Remeber the Therac-25?
>I certainly do since I have worked on embedded systems and medical equipment
>(diagnostic, not theraputic) and learned about it then. Also the names in the
>article brought back the memories:
>
><quote>
>The appeals in Panama were filed by a second defendant, MDS of Toronto. MDS
>owns Theratronics International Corp., which manufactured the Cobalt-60 machine.
>
>Theratronics and its previous owner, Canada Development Investment Corp., are
>also defendants.
></quote>
>
>Surely this is different equipment and the names (Theratronics and Therac-25)
>are unrelated??
Yes, I would think so. The names are related though: THERAC:
THErapeutic RAdiation Computer.
The Therac-25 was a joint venture between Atomic Energy Canada
and a French company that I do not remember the name of. It might be
in the article cum report by Dr. Nancy Leveson on the Therac-25
disasters. (It is about 50 pages of good reading.)
Sincerely,
Gene Wirchenko