SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ERD Model Assistance


ERD Model Assistance

Author
Message
mmoreauza
mmoreauza
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Hey all.

I'm busy revising for an exam and I'm going through ER Modeling. I have an example that I've attempted but I'm not sure if it's correct.
Perhaps somebody could point out if what I've done is correct?

Thanks very much.


Steve Jones
Steve Jones
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: Administrators
Points: 65281 Visits: 19118
The only thing that seems off is the GameTeam table. Why do you just not have two teams in the Game table thet FK back to the Team table? There isn't really a need to break that up that I can think of right now.

Otherwise, I think you've modeled it pretty good.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ian Scarlett
Ian Scarlett
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2291 Visits: 6894
I agree with Steve... a game is always between 2 teams, so I would get rid of GameTeam and just have a foreign key to Team for HomeTeam and AwayTeam in the Game table.

I would also have GamePlayer as the many-to-many link between Game and Player. Goal should then be a child of GamePlayer only. The way your diagram looks, the only route from Game to Player is via Goal... if a player doesn't score a goal there will be nothing to link a Player to a Game.



mmoreauza
mmoreauza
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
Thanks for the quick reply.
I see what you mean and that does seem more efficient.
Thanks for pointing out the goal story, I have no idea why I put it there Smile

I've done one more example, is this one correct?

Thanks a lot for your time, I really appreciate it!




Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14702 Visits: 12238
The Stumble Inn diagram is missing checkin information from Booking.

The link between Booking and Invoice is marked 1-1 in both directions, but it isn't really: there will be bookings for which there is no invoice (your booking data correctly omits any reference to invoice).

Tom

mmoreauza
mmoreauza
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 9
I see what you mean. Should I rather link invoice to customer then?
Tom Thomson
Tom Thomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14702 Visits: 12238
mmoreauza (6/3/2012)
I see what you mean. Should I rather link invoice to customer then?

No, I think that you should just change the annotation on the link between invoice and booking. I'm not sure what annotation you would use in an ERD, because I dislike them so I don't touch them or remember much about them.

There's an injection on invoice into booking, but it can't be surjective and there can't be any complete mapping on booking to invoice, since your description says the invoice is created on departure not on booking; so the mapping in the reverse direction is incomplete, not an injection on booking into invoice. Your annotations seem to say that the relationship include an injection on booking into invoice as well as in the opposite direction, because you mark both directions with "1:1" which is the standard notation in maths (category theory, general algebra, topology, set theory, and relational algebra) to indicate an injection. I would simply remove the 1:1 mark relating to the direction where it isn't appropriate, but I don't know if your style of diagram allows that or not.

In SQL terms, or in terms of Codd's relational theory with nulls, this would man that booking can be a foerign key in invoice, but if you want invoice to be a foreign key in booking you have to allow that field in booking to contain null; in practical database implementation terms you would simply not try to make invoice a foreign key in booking (that's why I pointed out that your not having the field was correct - you hadn't made a very common beginner's mistake), so that you don't hit that problem and don't need to introduce a nullable field - if you want fast discovery of invoice from booking you just provide an index on the booking field of invoice. That of course is also what you would have to do in realtional theory without nulls.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search