2

Lets suppose I have a scenario with the following model: An Animal table which represents any animal, a Dog table and a Bird table, each one with a 1:1 relationship with the Animal table.

Animal

    INTEGER id (PK)
    STRING name

Bird

    INTEGER id (PK FK referencing `Animal.id`)

Dog

    INTEGER id (PK FK referencing `Animal.id`)

(I'm giving only the keys just to be clear)

How can I guarantee that a given row in the Animal table will have JUST one referenced row in either the Dog or the Bird table? The model itself allows it...

An animal can't be a Dog and a Bird at the same time (not in mythology but that's not the case :P)

It would be better if this could be done just by playing with the model, without triggers...

Any tip would be appreciated :)

everton
  • 7,579
  • 2
  • 29
  • 42
  • 1
    Google disjoint subtype and you'll find loads of info on this problem. The general concensus seems to be that @TonyAndrews answer is the best way.. – StevieG Aug 11 '11 at 13:13
  • 1
    http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx – nvogel Aug 11 '11 at 13:30
  • great link! it exposes the approach that @TonyAndrews gave us. – everton Aug 11 '11 at 13:48
  • I just noticed that this is pretty much a duplicate of another question with [pretty much the same accepted answer](http://stackoverflow.com/questions/561576/polymorphism-in-sql-database-tables/562030#562030) as @Tony Andrews'. – Spycho Aug 11 '11 at 14:27
  • Sorry, i haven't found it before asking, we must agree the title doesn't helps – everton Aug 11 '11 at 14:36

4 Answers4

3

This may be what @Pranay meant, but the answer was incomplete. Add a column TYPE to all tables and then constrain it like this:

create table Animal (id integer, 
                     type string,
                     name string,
                     primary key (id),
                     unique (id, type)
                    );

create table Bird (id integer,
                   type string default 'BIRD' check (type='BIRD'),
                   primary key (id),
                   foreign key (id, type) references Animal (id, type)
                  );

create table Dog (id integer,
                  type string default 'DOG' check (type='DOG'),
                  primary key (id),
                  foreign key (id, type) references Animal (id, type)
                 );

See David Portas's blog fora good explanation of this.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • I think the OP wanted a solution that didn't have these kinds type checking constraints? Also, the type field on the Bird and Dog tables are effectively duplicate / unnecessary data (although it could be considered just part of the foreign key). It should work though :) – Spycho Aug 11 '11 at 12:23
  • Oh, also, that is database engine specific. That will work for SQL Server / Oracle / MS Access but not MySQL, which has slightly different syntax. You could use the general CONSTRAINT syntax though, which would achieve the same thing and be engine independent. Here's some [docs](http://www.w3schools.com/sql/sql_check.asp). – Spycho Aug 11 '11 at 12:27
  • 1
    @spycho: OP said they didn't want triggers, just "playing with the model" - and that's what I have done. Agree the syntax may be different on different DBMSs, but presume OP can adjust. – Tony Andrews Aug 11 '11 at 12:43
  • That's one good solution :) even thought it's weird to have a fixed type attribute on the Bird's and Dog's table.. – everton Aug 11 '11 at 12:52
  • Yes, if it was me I would either go with this solution or have a trigger when inserting to bird or dog that checks that the animal id doesn't already exist in another animal table. With Tony's constraint solution, the tables look a little odd, you have composite primary keys (not necessarily a bad thing) and it doesn't look as though it is in normal form (not always a problem). With the trigger solution, the triggers have the potential to get bigger and bigger as you add more types of animal. You could probably code that sensibly though, so that it doesn't take much effort to add another type. – Spycho Aug 11 '11 at 13:33
  • Even though the model becomes a little odd, it's the best approach so far without triggers. – everton Aug 11 '11 at 13:49
  • And to power up the solution, the 'type' attributes could be part of an Enum (depending on the DBMS, at least in postgres AFAIK). – everton Aug 11 '11 at 13:53
  • 1
    With a default value in the subtype tables (see amended answer) you don't actually have to specify the TYPE column when inserting. – Tony Andrews Aug 11 '11 at 13:53
  • Yup, with default values plus enums consistency, it appears to be the best way to deal with it.. – everton Aug 11 '11 at 13:55
  • Of course, this doesn't prevent setting up an Animal with no row in any of the subtype tables. That is a bit more tricky... – Tony Andrews Aug 11 '11 at 13:59
  • For that, you need a trigger :) – Spycho Aug 11 '11 at 14:06
  • Tecnically, we need to create the Animal row first anyways, and at that time no referenced row exists in any subtype tables. – everton Aug 11 '11 at 14:09
  • 1
    One other thing to note: the cited article state that this solution conforms to 5th normal form. I would say it violates 3NF. Each attribute not part of the primary key must non-transitively depend upon the whole of the PK. If your Animals have an age attribute, the age must depend upon the whole of the PK. I would say that age depends upon the id of the animal but not its type. You could fix this by removing type from the PK. Type isn't something that uniquely identifies the animal. It's probably not a problem in this instance though. – Spycho Aug 11 '11 at 14:10
  • 1
    @Everton, regarding creating the Animal row first: If you insert both Animal and animal sub-type rows as part of a transaction, you can do them in any order you feel like if you are just using constraints. Constraints are turned off during transactions and turned on again afterwards. This is so that you can force an Animal to only be inserted if a Dog for that Animal exists whilst also forcing a Dog to only be inserted if an Animal for that Dog exists. If, when you come to end the transaction, constraints are violated, it will throw an error and roll-back. – Spycho Aug 11 '11 at 14:14
  • For a different, easier example, it is feasible to only insert a customer row if they have bought something (i.e. have at least one row in the sale table), and to only insert a sale row if there is a customer to reference for that sale. – Spycho Aug 11 '11 at 14:16
  • Actually, regarding normal form... If the ID of an animal does not uniquely identify it, I think it's OK: Bird number 5 is aged 3. Dog number 5 is aged 7. The age is dependant on not just the id of the animal, but the type of the animal as well. – Spycho Aug 11 '11 at 14:21
  • 1
    @Spycho, you are right about violating 3NF. The Type attribute in Bird and Dog actually violates 2NF. It's a great shame that SQL's lame support for referential integrity forces the use of hacks like this. – nvogel Aug 11 '11 at 14:41
  • So it does... Good point. I knew something smelled bad. Still, it's not always the wrong thing to do to break normal form. It depends on the circumstance (e.g. performance critical queries). – Spycho Aug 11 '11 at 14:49
  • Yeah, sometimes it's a matter of Tradeoff's – everton Aug 11 '11 at 15:03
  • The principle is good, but just to nitpick: 1. I don't see what "unique (id, type)" does for you. If id is the primary key, it must be unique by itself. Saying that id+tye is unique is a tautology. 2. You could solve the denormalization problem by saying that the primary key is type+id rather than just id, and make no promises about the uniqueness of id alone. That would make joins require two fields instead of one, and I really can't think of any advantage other than allowing you to check a box and say "yup, it's normalized". But it would let you do that if you're pedantic. – Jay Aug 11 '11 at 17:26
  • @Jay, in Tony's original solution, that's how it was (see edits). It still violates normal form that way though. But yeah, it arguably doesn't matter. – Spycho Aug 11 '11 at 20:48
  • Yes, it was dportas who restored (ID) as PK, not me. But @Jay, re 'I don't see what "unique (id, type)" does for you' the answer is: it makes it possible to ensure that an Animal is not both a Dog and a Bird - which is what the OP was asking. Yes it's a tautology, but a useful one. – Tony Andrews Aug 12 '11 at 09:04
  • @Tony: Maybe I'm missing something, but if id is unique -- as it must be if it's the primary key -- then declaring that id+type is unique creates no additional restriction. You cannot have a 17,Bird and a 17,Dog because you can only have one record 17. The foreign keys in the "subclasses" combined with the presence of the type fields prevent two different subclasses from pointing to the same record. – Jay Aug 12 '11 at 15:19
  • You are missing that without the (ID,Type) foreign key you could insert Animal(ID=17), Dog(ID=17) and Bird(ID=17), thus creating an Animal that has both a Bird and a Dog subtype - exactly what the OP asked how to prevent. – Tony Andrews Aug 12 '11 at 15:21
  • This technique has a name: Shared Primary Key. – Walter Mitty Nov 05 '17 at 20:53
1

In you databayse you can add one more column in you table like AnimalType

AnimalTable 
   Id
   AnimalType -- 1 = dog, 2= bird, 3= other
Pranay Rana
  • 175,020
  • 35
  • 237
  • 263
  • 1
    What if the Bird table stores something like wing span? You shouldn't put that in the Animal table (because not all animals have wings). Hence the need for another table. – Spycho Aug 11 '11 at 12:03
  • 1
    Yeah, with this I could create Views for each type of animal, but my goal is to store separate data for each type of animal, without flooding the model. – everton Aug 11 '11 at 12:13
  • If you do that, then you are shifting your problem to enforcing consistency between the value in an AnimalType column, and the particular table that the AnimalID appears in. – Erwin Smout Aug 11 '11 at 13:01
0

I don't think that this can be done without triggers / constraints whilst keeping the tables separate.

Spycho
  • 7,698
  • 3
  • 34
  • 55
-2

I feel that the database design here is wrong. The reason I say that is it seems that for every new animal you have to create new table. In general you want to create a database in the way so that every time you need to add data the schema does not change.

Here is one way to do it:
Table animals
animal_id PK
name

Table animailProperties
property_id PK
name

Table animalDecription
animail_id FK
property_id FK
property_data

Example:

Table animals
1 DOG
2 Cat
3 Bird

Table animailProperties
1 legs
2 wings
3 fly

Table animalDecription
1 1 4 (dog legs 4)
1 2 0 (dog wings 0)
1 3 0 (dog fly no)
2 1 4 (cat legs 4)
2 2 0 (cat wings 0)
2 3 0 (cat fly no)
3 1 2 (bird legs 2)
3 2 2 (bird wings 2)
3 3 1 (bird fly yes)

Something along these lines. So you can create any type of properties for every possible animal.
Every time you need to create a new animal, you just assign correct properties to it.

I hope it helps.

Andrey
  • 1,808
  • 1
  • 16
  • 28
  • 1
    http://stackoverflow.com/questions/4011956/multiple-fixed-tables-vs-flexible-abstract-tables
    What about this post?     – Andrey Aug 11 '11 at 17:52