I'm trying to work out the best way of creating SQL Server primary keys, foreign keys and constraints to accurately represent my data model in LINQ / Entity Data Objects. Let's assume – for the purposes of simplification – that I have four main tables – Cats, Dogs, Pets and Owners – and one association table: OwnersToPets:
create table Cats (
ID_Cats int NOT NULL IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
miceEaten int NULL
)
create table Dogs (
ID_Dogs int NOT NULL IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
favouriteToy nvarchar(50) NULL
)
create table Owners (
ID_Owners int NOT NULL IDENTITY PRIMARY KEY,
Name nvarchar(50) NOT NULL,
)
create table Pets (
ID_Pets int NOT NULL IDENTITY PRIMARY KEY,
ID_Cats int references Cats(ID_Cats) NULL,
ID_Dogs int references Dogs(ID_Dogs) NULL
)
create table OwnersToPets (
ID_Owners int references Owners(ID_Owners) NOT NULL,
ID_Pets int references Pets(ID_Pets) NOT NULL
)
When I create my entity objects by importing from the database, I get the following:
The problem is the relationship between Pets and Cats / Dogs. Each Cat / Dog has a single Pet reference. Each Pet object should have one Cat reference and one Dog reference, one of which will always be null. Conceptually, at least, there is a 1-to-0..1 between Cats / Dogs and Pets.
However, the entity data model created 0..1-to-many relationship between Cats / Dogs and Pets.
Any suggestions? Obviously, my database already exists and is currently in use with version 1 of the application I'm writing version 2 of, so I'd prefer not to have to change the database too much.
Best Answer
There is a lot of info missing in your question
The problem with your current model (as shown) is the Pets table: it adds no value currently. Of course, your simplified design now hides information that probably makes it useful...
I'll throw in some ideas with assumptions though.
Note: This is correct design, whether EF can deal with it or not. That is, you design the model, implement the database, then make your client work with it
Option 1:
Assuming an owner can have 0..n dogs and/or 0..n pets, then you'd need 3 object tables and 2 link tables. You don't need Pet.
This allows multiple owners per Dog and Cat. You can of course just have an OwnerID in Dog and Cat to restrict to one owner.
You can have different attributes for Cat and Dog here.
As you add more types, this becomes unwieldy (no matter how you model owners)
Option 2:
Do you need to distinguish Cat or Dog as separate entities? Isn't this just a type attribute of Pet?
You can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).
This requires each Cat or Dog (or Rabbit, Snake etc) object to have the same attributes.
Option 3:
The final way is superkey/subtype.
This is is useful because if combines elements of options 1 (different attributes per Cat and Dog) and option 2 (keeps the idea of Pet)
This defines one Pet object that has exactly one sub type of Dog, Cat, Rabbit, Snake etc. The super key comes from the extra UQ1 in Pet to enforce subtype. Pet as a table also stores common attributes.
Note: UQ1 is a unique constraint used by the FKs in Cat and Dog. It is a "super key" on Pet that allows sub-types via the FK
Again, you can have a link table PetOwner (multiple owner) or OwnerID as an FK in Pet (single owner).
Note, PetType can be a constant derived column in Dog and Cat
Edit: added tables for Pet, Cat and Dog.
I assume Owner is obvious...
Notes:
The PK of cat and dog can be
PetID
orPetID, PetSpeciesID
. The latter is better to match the foreign key.The unique constraint on cat and dog is optional, really, as the index is contained within the PK and PetID is unique anyway if PetSpeciesID is constrained to a single value.