Wrox Programmer Forums
|
BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3
This is the forum to discuss the Wrox book Beginning ASP.NET 3.5: In C# and VB by Imar Spaanjaars; ISBN: 9780470187593
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning ASP.NET 3.5 : in C# and VB BOOK ISBN: 978-0-470-18759-3 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 12th, 2010, 06:50 AM
Authorized User
 
Join Date: Apr 2009
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default Database structure PK - FK

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
 
Old January 12th, 2010, 07:27 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old January 12th, 2010, 09:48 AM
Authorized User
 
Join Date: Apr 2009
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

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!
 
Old January 12th, 2010, 11:11 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
robbaralla (January 13th, 2010)
 
Old January 13th, 2010, 07:33 AM
Authorized User
 
Join Date: Apr 2009
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

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
 
Old January 13th, 2010, 03:15 PM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
The Following User Says Thank You to Imar For This Useful Post:
robbaralla (January 13th, 2010)
 
Old January 13th, 2010, 03:30 PM
Authorized User
 
Join Date: Apr 2009
Posts: 48
Thanks: 16
Thanked 0 Times in 0 Posts
Default

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

Saluti, Robin
 
Old January 15th, 2010, 01:05 PM
Authorized User
Points: 267, Level: 5
Points: 267, Level: 5 Points: 267, Level: 5 Points: 267, Level: 5
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2010
Posts: 56
Thanks: 17
Thanked 2 Times in 2 Posts
Default

Quote:
Originally Posted by robbaralla View Post
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
The Following User Says Thank You to ken evans For This Useful Post:
Imar (January 15th, 2010)





Similar Threads
Thread Thread Starter Forum Replies Last Post
setting the value of FK csharpa SQL Server 2005 1 March 8th, 2007 01:50 PM
setting the value of FK csharpa .NET Framework 2.0 0 February 28th, 2007 10:53 PM
setting the value of FK csharpa ADO.NET 0 February 28th, 2007 10:50 PM
Database Structure aw23 VB Databases Basics 2 October 15th, 2005 01:20 PM
How to hide Access database structure pavel Access 1 September 2nd, 2003 02:54 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.