T-SQL Database Relationships PK FK Same Name!?

Question

Scenario

I have 3 database tables. One is for Images and the other two are People & Places. Since each person can have many images and each place can have many images, I want to have a ONE TO MANY Relationship between both people and images, as well as places and images.

Question

Does the foreign key have to be called the same name as the primary key? Or is it possible for me to call the Foreign key in the images table something generic, for example "PKTableID". This way I only need one image table.

Help greatly appreciated.

Regards,

EDIT:

The reason for wanting to have only a single image table, is because each image only refers to a single other table. As well as this, I used the example here of two tables, the actually database I will be using will have 20 tables, so I wanted to know whether it was still possible to use a SINGLE IMAGE TABLE FOR 20 ONE-TO-MANY RELATIONSHIPS?

Answer 1

EDIT

If one image is only ever owned by one of the twenty tables, this design might work:

People (PersonId, Name)
Places (PlaceId, Name)
Dogs (DogId, Breed)
Doors (DoorId, Height, Width)
Images (ImageId, ImageBinary, OwnerId, OwnerTable)

Where OwnerTable is the name or the code for the table that OwnerId belongs to.

This would save you 20 FKs in the image table, or 20 association tables. Then, in the joins, you would specify OwnerTable, depending on the table you are joining to.

You would need to use convertable types for the Ids (eg, TINYINT, SMALLINT, and INT), and preferably one type for all (eg, INT), and you would have to manage referential integrity yourself though triggers or some other code.

/EDIT

You need 5 tables, not 3:

People (PersonId, Name)
Places (PlaceId, Name)
Images (ImageId, ImageBinary)
ImagesPeople (ImageId, PersonId)
ImagesPlaces (ImageId, PlaceId)

You can call the fields whatever you want. People.Id, ImagesPeople.PersonId, etc.

But what you can't do is something like this:

People (PersonId, Name)
Places (PlaceId, Name)
Images (ImageId, ImageBinary, PlaceOrPersonId)

Well, you can, but the database won't help you enforce the relationship, or tell you which table the FK belongs to. How would you know? There are hackish work-arounds like staggering the id increments, adding a type column to Images, or using GUIDs.

Alternatively:

Things (ThingId PK, Type)
People (ThingId PK/FK, Name, Age)
Places (ThingId PK/FK, Name, LatLon)
Images (ImageId PK, ImageBinary, ThingId FK)

You could also make Images a "Thing". Sometimes you see designs like this. It does give you referential integrity, but not type exclusivity. You could have the same ThingId in People, Places and Images, and the database wouldn't care. You would have to code that rule yourself.

Edit: at Cylon Cat's suggestion, scenario 4:

People (PersonId, Name)
Places (PlaceId, Name)
PeopleImages (PeopleImageId, ImageBinary)
PlaceImages (PlaceImageId, ImageBinary)

Here, images are exclusively owned by one person or place. Similar to version 2, but with declared foreign keys. It may have some performance benefits vs the 5 table design, since fewer joins are required. You lose "Image" as a distinct entity, replaced by "PeopleImage" and "PlaceImage".

Answer 2

As Peter pointed out you really need a many to many relationship, unless you want to restrict your images to pictures with only one person, also, a good place index will reflect the hierarchical nature of place names (Montmartre, Paris, France = three possible names for one place).

Now technically you can call your indexes and tables anything that is not a reserved word and has not been used already, X, Y , Z12345 and WOBBLY are all valid names for an index.

In practice however its best to follow a naming convention that points to whats being stored and what for. So tables PEOPLE_X_IMAGES and a PLACES_X_IMAGES would be a good idea in your case. You dont really need anything about people or places in the actual images table. Likewise you dont need anything about images in the PEOPLE and PLACES tables.

Answer 3

You can use one table but you would need twenty different fields for the relationships. If you set up a foreign key relationship, then all the data must relate to the parent table, you can't store two foreign key relationships in one table. So you must set up a column for each fk you want to have.

Answer 4

You could add theoretically add two foreign keys to the images table, one for People and one for Places. Then you could allow nulls and join on the appropriate columns when running a query. This is not much of a solution though because what does this table look like when you have 14 tables that need to join with it?

If you are not going to use GUIDs, then I say you set it up as many-to-many for the sake of the next guy that has to understand it.

Answer 5

How about

Person (PersonId PK, Name, ImageId FK)
Image (ImageId PK, Name)
Place (PlaceId PK, Name, ImageId FK)