More Foreign Keys than primary keys

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

More Foreign Keys than primary keys

Author

Topic

edwardtong694
Starting Member

2 Posts

Posted - 2009-11-06 : 13:34:20

Hi Guys

I really need some help this is driving me mad!

If for example I have a database that holds Events there are ten Events taking place so I have ten primary keys. However there are 25 people attending the event so my forien key is Attendees which links to a table full of attendees.

In the event table do I have to extend my primary keys to 25 to accomodate the attendee foreign key even though there only 10 events?

I Hope someone can help

aplogies if this is a stupid question.

Thanks in advance.

Edd

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-06 : 18:07:53

Table Event
PK -- EventID

Table Person
PK -- PersonID

Table Attendee
-- EventID ---> FK to Event(EventID)
-- PersonID ---> FK to Person(PersonID)

Make EventID, PersonID unique

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-11-06 : 19:39:15

Can you show us your table definitions? What do you mean by "so I have ten primary keys" and "extend my primary keys to 25"?

In general, though, it sounds like you want to model a many-to-many relationship. An event can have many attendees and an attendee can attend multiple events. If so, you want to define a "junction table" that links the Events table and the Attendees table.

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-11-06 : 21:26:43

isn't that what i just showed?

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-06 : 22:13:17

You do know that a value in a Primary key is just 1 set of unique value(s)...in your case 2

You only have 3 tables. Each has it's own primary key. That's 3 Primary Key's.

Your third table is a junction table which is a child of the other 2. So you have 1 Foreign key

Or are you discussing the cardinality of the data?

Oh wait...I think I see...are you saying should you pre-populate the child with ALL the possible combination?

That answer would be absolutely not.

It is the ABSENCE of these relationship that are just as important as the ones that DO exists.

Clear as mud?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

edwardtong694
Starting Member

2 Posts

Posted - 2009-11-07 : 03:59:08

Hi Guys

Thanks for your replys it is greatly appreciated.



Event3  = (Event ID, Event Date, Event Location , Booking ID*,Course ID*,VenueID*)	

Delegate 3= Delegate Id, Name, Address, Telephone, Presenter/Delegate, ) 	
	
Booking3 =  (Booking ID, Delegate ID*,Event Id,* Booking Date, Booking Fee)

Course3 = (CourseID, Course Title, Course Duration, Course Fee)	
	
Venue3= ( Venue ID, Hotel Venue, B+B Fee)

)

This is my normalisation in third normal formlike I say there is 10 events and over 15 bookings.

Go you think I need to create an extra entity then?

Thanks again

Edd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources