0

I'm working on a database for a racing game. In this diagram, a race must happen on a track (so TrackID must be referenced on table Race), and I may have a track where many to no races ocurred:

enter image description here

So minimum cardinality from Race to Track should be 1, setting the relationship as Identifying. But that would also make TrackID on table Race become a PK. And I don't see why I would need that. So I thought I'd rather have it as a 'required' FK; what would change besides just not having it as a PK? With TrackID as a FK on Race, Microsoft Visio automatically sets that minimum cardinality as 0, which is making me scratch me head...

I am new to modeling databases, and this question might make it evident, but please help me understand this.

Barbaroto
  • 33
  • 7
  • I just bumped into this: https://stackoverflow.com/questions/6095527/is-this-a-flaw?rq=1 So... is this a flaw in visio? – Barbaroto Sep 08 '17 at 05:14
  • Please tell us what information modeling reference you are using. Why do you think you care about a "difference"? A *subrow value* is a PK or FK when it appears in a PK or FK column list. PK & FK column list declarations say things about what table values can arise. We declare them so the DBMS enforces them. We don't need them to query. (Some collections of declarations enforce others, that we thereby don't need to declare.) What do you mean "I don't see why I would need that"? TrackId is not a PK of Race.You misunderstand "identifying". See *definitions* of "identifying", PK & FK. – philipxy Sep 08 '17 at 08:28
  • I am only concerned about having no races without a track really; I said I didn't see why I would need TrackID as a PK because I don't want to specify the track everytime I search for a race. TrackID isn't a PK of Race on the picture because I set it that way, I made it a req'd column, and even though it is required, the minimum cardinality from Race to Track still is zero, which at least conceptually doesn't assure every race to happen on a track (that's what I was trying to show). Why is that? That's what is mainly confusing me. – Barbaroto Sep 08 '17 at 09:02
  • Indeed I got a bit confused with "identifying relatioships", I'm sorry. And coming to think of it, it seems that having TrackID as a primary key of Race might be the _correct_ way of doing this. – Barbaroto Sep 08 '17 at 09:03
  • But my question persists on the cardinality matter, having the FK as req'd. That's what made me care about the possibility of there being _difference_. – Barbaroto Sep 08 '17 at 09:07
  • 1
    There are two separate issues here: what design you want & how to get it into your tool. I was addressing the odd way you wrote about basic terms and perhaps misunderstood/misunderstand them. Re wrestling Visio: There are different ways of labeling cardinalities. I've read it is natively IDEF1X (see Wikipedia 'E-R Model' re constraints.) Is the *database* the way you want, even if the diagram isn't? Visio may not easily support the convention you are using. That is one reason I am asking you to tell me what your Iference is. Read the documentation. ... – philipxy Sep 08 '17 at 09:50
  • If you don't want a weak entity & identifying relationship, try creating the entity/table first, then add the (Required) relationship. Also, did you try the workaround in you comment's link? – philipxy Sep 08 '17 at 09:59
  • 1
    For clarification of identifying relationships vs mandatory roles in the ER model, please see my answer to [Is optionality (mandatory, optional) and participation (total, partial) are same?](https://stackoverflow.com/questions/38034049/is-optionality-mandatory-optional-and-participation-total-partial-are-same/38035173#38035173) – reaanb Sep 13 '17 at 05:26
  • @philipxy yes, I did, but it didn't change anything, and doesn't really matter anymore, as I got my way around it with all the answers/comments in this topic. Thanks for the help and attention, I appreciate it very much. Sorry I took so long to answer. – Barbaroto Sep 25 '17 at 12:51

3 Answers3

1

There are two ways to identify a race. You can assign each race a unique RaceId, in which case the RaceID identifies the race. This is the common way almost all designers will go. It's the way I would go, in the absence of any reason not to.

It's also possible to assign a RaceID that is only unique within a single track. That is, there could be two races with Id 123, but one is on Track 1 and the other is on track 2. I can't think of any reason for doing things this way, but it could be done. There are other use cases where this kind of context dependency makes sense, but not this use case.

In the first case, which is what you have diagrammed, TrackID would be an FK, but not a component of the PK. It could be constrained to be not null, but that doesn't make it part of a PK.

In the second case, the TrackId in the Race table would be an FK as before. But the PK would now include both the RaceId and the TrackId. This might be what you meant by identifying relationship.

I'll not in passing that the Lap table seems to use LapId in the manner I described as the second case. A lot of races have a lap 5, but you have to know both the Lap Id and the Race Id to know which lap you are talking about.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
1

This is a fairly complex topic, and one that's been discussed on SO before.

Let's leave aside whatever Visio does - many ERD tools are too clever, or buggy.

An identifying relationship is one where the child (race) simply cannot exist without the parent (track), and where the child's primary key includes the parent's primary key. A common example is an order (parent) with order lines (child) - the order line's primary key might be a combination of the order's primary key and a sequence number. When you delete the order, you delete all the order lines. The foreign key on the child is immutable, and mandatory.

A non-identifying relationship is one where the child can exist without the parent, or where the parent key might change.

Non-identifying relationships can be either mandatory or optional. It is false to state that a mandatory foreign key requires an identifying relationship.

I believe that in your case, you have a non-identifying relationship - races can be scheduled, but then change track. I do not believe "track_id" should be immutable; it may even be the case that races are scheduled before the tracks are agreed, so the relationship may be optional.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

The relation from track to race is {1}:{0,n}, i.e. every race is performed in exactly one track, and every track has zero to many races.

  • The track ID uniquely identifies a track and hence makes for the primary key of this table.
  • The race's primary key is the race ID for the same reason.
  • Moreover there is a track ID in the race table, so as to establish a {0,1}:{0,n} relation. Make this track ID non-nullable and you get {1}:{0,n}, which is what you want.

This doesn't make the track ID a primary or unique key for the race. Far from it. It is not unique in the table (as many races can be performed in the same track).

I don't know, however, how Microsoft Visio implements nullability/non-nullability to tell {1}:{0,n} from {0,1}:{0,n} relations.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73