| Author |
Topic |
|
Tim
Starting Member
392 Posts |
 Posted - 2003-04-28 : 10:42:27
|
super type is "Vehicle"sub types are "Car" and "Motorcycle"Each vehicle must be either car OR motorcycle. create table Vehicles(vid integer identity not null ,TopSpeed integer not null)create table VehiclesCars(vid integer not null ,NumberOfDoors integer not null)create table VehiclesMotorcycles(vid integer not null ) Q1) how to implement the disjointness constraint so that each vehicle is one and only one subtype, and in a way that elegantly accomodates many more subtypes ("Truck","Tractor","Pope Mobile"... etc) ?Q2) how to implement the completness constraint when a row must exist in the vehicle table and in one of the subtype tables. ie, How to escape the deadlock of each table requiring a row in the other?thanks for any help |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-04-28 : 11:20:12
|
| damn i searched before I posted too :)So I read that thread. Jay's is effectively using a view for the supertype and putting all of it's attributes in every subtype.My example is obviously a contrived one for clarity, but in the actual case, there are many attributes in the supertype and many subtypes, so Jay's answer to it is still messy for me. Also, if every vehicle has a unique registration code, then it becomes messy to enforce it without the supertype table. But as soon as I create that supertype table, I have the deadlock problem if I want to implement the RI. AndJeff your solution didn't seem to cover the completness constraint since it would be possible to add a row to the supertype without the possibility of there being no corresponding subtype row.arrgghh |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-28 : 11:26:51
|
| One table can be dependant on a row in another table, but it can't be both ways ... that is not possible. It'd be a chicken and egg thing.In any RDMS, you can have a table of Orders and a table of OrderDetails --- but there is no way (that I know of!) to say an Order MUST have rows in Order details. Of course, it is trivial to say the rows in the details table must match up with an Order.If there's a way around this, I'd sure like to know -- but I really don't think it's possible.- Jeff |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-04-28 : 11:29:42
|
| yeah that's why it bugs me.I can model this logically and then can't implement it physically. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-28 : 12:07:11
|
quote:
yeah that's why it bugs me.I can model this logically and then can't implement it physically.
Ummmm...How?Did use any Modeling tools like ERWin?How can you model a fact that a Parent must have a child?I imagine you could probably manage this with a trigger, but I would also imaging that you'd have to keep your hands off those tables a well, or risk a syncronization problem later.Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-28 : 12:18:56
|
quote:
Ummmm...How? Did use any Modeling tools like ERWin?
You can use notepad to model something logically if you want, Brett."All Veichles must have 1 corresponding record in the viechle-sub-types table."Done!- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-04-28 : 12:42:30
|
| ...yes, but it's the "in 1 table but not the other table" that defies data modelling techniques....never seen any notation that does that.I guess you can call it a business rule...but not a relational; model rule (or one I've never encountered).Brett8-)EDIT: OOOPPPPS I take it back, ERWin has a SubType Category relationship....but that's only in the logical model....Edited by - x002548 on 04/28/2003 12:50:59 |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-04-28 : 22:22:03
|
| Seems I will have to live with the fact that I can't enforce this rule "declaritively" (if that's even a word) and in a way that is database independent.Edited by - tim on 04/28/2003 22:26:02 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-04-28 : 22:33:20
|
| I suppose you can write a trigger that forces an insert into the appropriate sub-table(s) whenever a certain super-type entity is created, and also have it cascade or rollback the deletion of a supertype to the subtype (and vice-versa). You would, by necessity, have to supply some default values for the initial creation of the sub-element and then update them afterwards, or, write a stored procedure to accept all of the relevant attributes and insert them correctly.However, I think you're trying to force an object-oriented structure into a relational database, and it really just doesn't work, and I don't think it's worth the effort to force it to. Apples are apples, oranges are oranges: neither is better than the other, nor are they both appropriate or interchangeable in the same dessert recipes. If you can't get the perfect tool for the job you have, you need to find some compromise that works with the tools you do have. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-04-28 : 22:46:52
|
| You can overcome the completeness constraint by using Views to insert your data.Create Views that join your "supertype" to your "subtype" and then write an instead of trigger for each. Disallow direct insertion into the supertype table....DavidM"SQL-3 is an abomination.." |
|
|
|
aiken
Aged Yak Warrior
525 Posts |
Posted - 2003-04-29 : 00:58:25
|
I may be slow, but I'm not sure I understand the intent.From where I sit, this should also work if rather than creating a seperate table for each subtype, you do:create table vehicles (vid int identity not null, topspeed int not null, i_types int not null)create table VehicleTypes (i int identity not null, typename varchar(30)) ...using seperate tables for each vehicle type rather than a simple join table is why this problem looks more complicated than it is, I think.Cheers-b |
|
|
|
Tim
Starting Member
392 Posts |
Posted - 2003-04-29 : 19:23:22
|
| aiken, where woul dyou put all the subtype columns? They are different for every subtype. I only showed two subtypes but there may be many more with many columns each... |
|
|
|
|
implementing generalization hierarchy