Michael Brosdorf
michael.broesdorf at web.de
Wed Jul 16 09:22:38 CDT 2003
Arthur, Charles, thank you for your comments. My application is actually not about vehicles, I used that just as an example (sorry for not making that clear). Actually it is not even a real generalization/specialization since the subclasses are totally different things. The only thing they have in common with the superclass are that they exist and that exactly one item from one of the subclasses is matched to an item of the superclass. To be precise: the superclass links dimensions (which are the subclasses) to certain key values, that are kept in yet another table. The dimensions are already existing tables in a pretty complex production database. Right now, the users want to assign the key numbers to a set of 4 dimensions (and they want to be able to use that with any combination of dimensions). But I have developed a certain mistrust in the demands of end users (especially when it comes to reporting...). Therefore I want to be able to add new dimension by simple creating an entry in a control table. That is the reason why I can't simply put the PK of the dimension tables in the table containing the key number values as foreign keys. But it looks like I will have to implement that using triggers.. A picture of the data structure I've come up with so far can be found at http://www.salabresam.de/RI%20Question.jpg The two highlighted field control the source table (ST) to which the ST_ID originates. Again, thanks for your comments! Michael -----Ursprungliche Nachricht----- Von: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]Im Auftrag von Wortz, Charles Gesendet: Mittwoch, 16. Juli 2003 14:40 An: dba-sqlserver at databaseadvisors.com Betreff: RE: [dba-SQLServer]Referential integrity question(Generalization/Specification) So how do you classify the cross-overs they are coming out with? Are they cars or trucks or both or neither? And how do you classify SUVs? Some are built on truck chassis and some on auto chassis. Unless the subclasses are mutually exclusive there is no way of implementing RI. If the subclasses are mutually exclusive, then you can write rules to prohibit the same ID being in two subtables. Charles Wortz Software Development Division Texas Education Agency 1701 N. Congress Ave Austin, TX 78701-1494 512-463-9493 CWortz at tea.state.tx.us -----Original Message----- From: Michael Brosdorf [mailto:michael.broesdorf at web.de] Sent: Wednesday 2003 Jul 16 02:53 To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer]Referential integrity question (Generalization/Specification) Dear group, for an application I need to implement a generalization of data objects. As an example, let there be 3 tables: 1. Vehicles 2. Cars 3. Trucks Every vehicle is, of course a vehicle with some common properties (for instance steering wheel diameter :-)). But then it is either a car or (xor) a truck, which both have different properties that apply only to the respective type of vehicle (e.g. number of passenger seats for cars etc.). Every vehicle has an entry in tblVehicles with a unique Vehicle_ID. Additionally, every vehicle has an entry in either tblCars (Cars_ID is PK, Vehicle_ID is FK) OR tblTrucks (with Truck_ID as PK and Vehicle_ID as FK). A given Vehicle_ID can be in only one of the specific table. Is there any way to enforce referential integrity in a scheme like that using SQL Server 2000 Constraints or other mechanisms? If not, has anyone implemented something like that (using triggers?) and would like to share his/her experiences? TIA, Michael _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com