Database structure PK

robbaralla · January 12th, 2010, 06:50 AM

Hi there readers, does someone out there has a bit of experience with database structure?

I am trying to build a site, based on the book, yet with a bit more complicated database structure behind it.
There are 3 main tables; places, accomodations and activities.
To all 3 I want to link photos from the photo table.
In solution 1, I used a foreignkey of a photoAlbum table (in the 3 main tables) that stands between the photo table and the 3 main tables, so I have an extra 2 tables.
This works, but strictly speaking the relationship between the photoAlbum and the accomodations (just to name 1 of the 3) is not really a one-to-many; it is a one-on-one; to one accomodation only belongs one photoAlbum and vice versa.

Solution 2: So then why not just leave out the photoAlbum and just put a FK of the accomodations in the photo table?

This however creates the problem that for all 3 main tables I now need a photo table, so that they can have their own FK column in the photo table.

So I now need 3 extra tables; 3 different photo tables, each with a different foreignkey column, but the relationships here are real one-to-many relationships.

Does anybody have any suggestions about the better solution of the two?

Best regards, Robin

Imar · January 12th, 2010, 07:27 AM

What about a junction table that keeps track of the primary key of the others and a type: E.g:

Code:

PictureId   ExternalId     PictureType
1           1              accomodations
2           2              accomodations
3           3              accomodations
4           1              activities
5           2              activities
6           3              activities

If you make the three columns a composite primary key, you can make sure you only have one type ID associated with a PictureId.

You can then queries for, say, an activity like this:

SELECT Path FROM Picture INNER JOIN YourJunctionTable ON Picture.Id = YourJunctionTable.PictureId WHERE Id = @someId and PictureType = 'activities'

Does this help?

Imar

robbaralla · January 12th, 2010, 09:48 AM

Ok Imar that was a big step! My intuition tells me that that is the answer, but my brain is lagging a bit behind....

So every Picture.Id has a pictureId in the Join table, one-on-one.

The externalId keeps track of the primary keys of my 3 main tables , so this is sort of a combined foreign key. And so in the Diagram I'd have to establish 3 FK - PK relationships.

And at last you need a picture type to seperate between the Ids in the 3 main tables that will be the same.

But then what about the composite primary key, if I do that, does that mean that I can only add 1 picture to each combination of type and externalId? I don't want that...

Ok, before my brain defragments, I'll go out and pick up some wood with the Land Rover and saw it to pieces.

Thanks!

Imar · January 12th, 2010, 11:11 AM

Quote:

And so in the Diagram I'd have to establish 3 FK - PK relationships.

Nope. You can't create a strong relation with this setup. It can't enforce the relation because you may have an ID of 236 in the activities table that doesn't exist in another table.

Quote:

if I do that, does that mean that I can only add 1 picture to each combination of type and externalId? I don't want that...

No, that's not the case. The compisitie key enforces records to be unique for all participating columns. So this is not allowed:

Code:

PictureId   ExternalId     PictureType
1           2              accomodations
1           2              accomodations

as it would assign an accommodation (1) to a picture (2) twice. However, this would work:

Code:

PictureId   ExternalId     PictureType
1           1              accomodations
2           1              accomodations

This assigns picture 1 and 2 to accomodation 1.

Does this help?

Imar

robbaralla · January 13th, 2010, 07:33 AM

Hi there Imar,

So, ok I think it begins to dawn on me, please correct me if I'm wrong:

Between the junction table and the 3 main tables I establish no relationship at all.

To get to the pictures that belong to the activity with Id=32, I could then query the junction table with Linq, after adding it to the datacontext, with the following

Code:

var ActivityPictures = from p in myDC.junctionTable
                            where externalId=32 && pictureType=activity
                            select p;

Is that correct? And is there anything against adding those 2 columns to the picture table and not using a junction table?
I could also make a PhotoType table were the Id column is used as FK in the picture table.

ok, I hope it's solved now thanks again,

Robin

Imar · January 13th, 2010, 03:15 PM

Quote:

And is there anything against adding those 2 columns to the picture table and not using a junction table?

You can certainly do that, and it would make things easier. However, it also means you can't reuse a picture between places, accomodations and activities.

Cheers,

Imar

robbaralla · January 13th, 2010, 03:30 PM

Ok, I get your point. Thanks again for your much appreciated help.

Saluti, Robin

ken evans · January 15th, 2010, 01:05 PM

Quote:

Originally Posted by robbaralla

Hi there readers, does someone out there has a bit of experience with database structure?

Well, I have a bit of (about 20 years) experience with data modeling and logical database structures, so I'd like to comment on this problem.

Firstly, its important to recognise that fundamentally, a database structure is a deductive logic system. With a correctly designed database, each table represents one or more "Facts" that can be expressed in natural language.
Futhermore in a properly designed data structure, there are no duplications.

So for example, your table structure (the data model) might contain the following "Facts".
Photo was taken at Place
Photo was taken on Date
Place is in Country
Place is in City

From these four "Facts" you can see that there will be a "Photo" table and a "Place" table.

It is good data modeling practice to uniquely identify the things that the facts are about, so the foregoing four facts might be shown as follows:
Photo(id) was taken at Place(id)
Photo(id) was taken on Date(yymmdd)
Place(id) is in Country(id)
Place(id) is in City(name)

From this you may be able to see that the Photo(id) will be the primary key in the Photo table and the Place(id) will be the primary key in the Place table.

The PK-FK structures are "just" links between those columns that are serving as unique identifiers for the "fact instances" that are contained in each table.

Where all this leads to is that with all but the most simple data models, you can never know what your eventual table structure will look like until you have defined the facts.

So, even your simple example and your discussion with Imar shows that its easy to spend a lot of time fiddling about with table structures.

My suggestion is that you begin by listing the Facts that you want your database to store. After that, your table structure should be easier to see.

Hope this helps.
Ken