0

Situation:

I have 2 tables:

Table 1

TrackID PK
random columns
ActionID FK

Table 2

ActionID PK
random columns
TrackID FK

The Question: Is there a problem with the above set if so what (normalization?) or is more information needed...?

Thanks

Suge

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
Suge
  • 39
  • 6
  • Yes you have a problem there for sure. How can you ever add a row to either table? The way you built these keys means you can't add a new row to either table unless the value for the foreign key already exists in the other table. This is a very poor design. – Sean Lange May 04 '15 at 14:59
  • There should be no need for both tables to have a FK to the other. You only need one to show the relationship. – Tab Alleman May 04 '15 at 15:00
  • The Track (TrackID) table is the main table. When an "action" is taken regarding a Track (row) the Action table gets a row and the Track table is updated with the ActionID. The ActionID in the Track table is not required. However the TrackID of the Action table is required... – Suge May 04 '15 at 15:05
  • The purpose: When I need information regarding an Action taken regarding an Track... I have the ActionID to reference a row in the Action Table. However, if I only need to list all TrackID that don't have an ActionID (no action taken) I just see which Track rows have no ActionIDs... A Action is taken for A Track (row gets added to the Actions Table, ActionID then gets inserted into the Tracks table based on the TrackID)... – Suge May 04 '15 at 15:16
  • Have only the TrackID as a foreign key in the Action table. If you want to know which tracks have no actions defined, use a Left Join. – TommCatt May 05 '15 at 16:41

2 Answers2

0

The provided schema models two entities being connected by two different relations.

The first relation R1 Track - * ---- 1 - Action is modeled by the field ActionID in Table 1

The second relation R2 Track - 1 ---- * - Action is modeled by the field TrackID in Table 2.

This could be a valid schema if your requirements indicate that you have two different relations.

A simplified example of this case would include two entities Employee and Project with

the relation

manages Employee - 1 ---- * - Project

and the relation

works Employee - * ---- 1 - Project

assuming that an employee works only for at most one project.

The question here is do you want to model one or two relationships?

If you want to model one relationship between your entities then your schema is somewhat wrong, otherwise it is right.

Stavros Zotalis
  • 718
  • 4
  • 24
0

Is there a problem with the above set?

No, there is no "problem" in the classical meaning of the word. You can stick to your design and it will have no problem. But, is it standard? that's what I will be discussing in the following section.

what (normalization?) or is more information needed...?

Yes, as I implied above, there are information I can provide you with in order to standardize or "normalize" your design. Unfortunately, your purpose of this database design is not quite clear. Knowing that purpose is essential in order to sufficiently answer the question. However, I will address two possible purposes which I suppose you've got one of them.

Purpose 1: every track can have one action at most: In this case you don't need two tables. You can have:

TrackID PK
random columns
random columns of actions /*(will stay null if no action takes place)*/

Purpose 2: every track can have zero or more actions: In this case you need two tables as the following:

Table 1:

TrackID PK
random columns

Table 2:

ActionID PK
random columns
CreateDate /*(search for creating a DateTime column with default to current date)*/
TrackID FK

So, if you want the latest or current action, you select top 1 with order by CreateDate desc with relation to Track.

yazanpro
  • 4,512
  • 6
  • 44
  • 66
  • The main reason I was staying away from a single table was due to the "Action" columns being null. A track can have 0 or 1 "Actions". A "Action" has to have 1 and only 1 "Track". – Suge May 04 '15 at 16:59
  • There is no problems with having the Action columns null when you don't have an action. That's not a bad design so you don't really need to stay away from it. If you like, you can have a flag column on the Track table to indicate whether the track has or has no action (you will still have nulls on the action columns) – yazanpro May 04 '15 at 18:00