1

Im having some 'perception' problem, maybe... I got this table linked multiple times with another small table and maybe is more of a personal problem with the way it looks but I feel there might be a more 'minimal (?)' way of doing this, or its just the ugliness of the diagram that upsets me, Anyway, here's what it looks:

+==============+
|   Records    |
|==============|
| PK RecID     |
| FK Action1ID |=========+
| FK Action2ID |=======+ |
| FK Action3ID |=====+ | |
| FK Action4ID |===+ | | |
+==============+   | | | |
                   | | | |    +=============+
                   | | | |    |  Actions    |
                   | | | |    +=============+
                   +=+=+=+===>| PK ActionID |
                              |    Descr.   |
                              +=============+

So basically:

CREATE TABLE Records (
  RecID INT PRIMARY KEY NOT NULL,
  Actions1ID INT,
  Actions2ID INT,
  Actions3ID INT,
  Actions4ID INT,
  FOREIGN KEY (Action1ID) REFERENCES Actions (ActionID),
  FOREIGN KEY (Action2ID) REFERENCES Actions (ActionID),
  FOREIGN KEY (Action3ID) REFERENCES Actions (ActionID),
  FOREIGN KEY (Action4ID) REFERENCES Actions (ActionID),
);

Which would translate when querying for a graphically descriptive resultset:

SELECT
  b.Action,
  c.Action,
  d.Action,
  e.Action
FROM
Records a
  INNER JOIN Actions b
    ON a.Action1ID = b.ActionID
  INNER JOIN Actions c
    ON a.Action2ID = c.ActionID
  INNER JOIN Actions d
    ON a.Action3ID = d.ActionID
  INNER JOIN Actions e
    ON a.Action4ID = e.ActionID

Am I missing something radically obvious?

I saw something simillar by searching on a forum and the suggestion was to add 2 new tables, but that look overkill in the long run. Suggestion goes about creating a table holding the 'Action' groups:

+====================+
|    ActionGroups    |
+====================+
| PK ActionGroupID   | 
|    ActionGroupName |
+====================+

+======================+
| ActionsTakenByRecord |
+======================+
| PK ActionTakenID     |
| FK RecID             |
| FK ActionGroupID     |
| FK ActionID          |
+======================+

So the diagram would be sort of like:

     +===================+
     |      Records      |
     |===================|
+===>| PK RecID          |
| +==| FK ActionTakenID  |
| |  +===================+ 
| |                         
| |    +=============+
| |    |  Actions    |
| |    +=============+
| |    | PK ActionID |<==========+
| |    |    Descr.   |           |
| |     +=============+          |
| |                              |
| |  +====================+      |
| |  |    ActionGroups    |      |
| |  +====================+      |
| |  | PK ActionGroupID   |<===+ |
| |  |    ActionGroupName |    | |
| |  +====================+    | |
| |                            | |
| |  +======================+  | |
| |  | ActionsTakenByRecord |  | |
| |  +======================+  | |
| +=>| PK ActionTakenID     |  | |
+====| FK RecID             |  | |
     | FK ActionGroupID     |==+ |
     | FK ActionID          |====+
     +======================+

And a SELECT * FROM ActionsTakenByRecord would look something like:

+=====================================================+
| ActionTakenID  |  RecID | ActionsGroupID | ActionID |
+=====================================================+
|       1        |   1    |        1       |    3     |
|       2        |   1    |        2       |    3     |
|       3        |   1    |        3       |    1     |
|       4        |   1    |        4       |    2     |
|       5        |   2    |        1       |    2     |
|       6        |   2    |        2       |    3     |
|       7        |   2    |        3       |    4     |
|       8        |   2    |        4       |    1     |
|       9        |   3    |        1       |    1     |
|       10       |   3    |        2       |    1     |
|       11       |   3    |        3       |    4     |
|       12       |   3    |        4       |    4     |
+=====================================================+

So basically since we have 4 Action groups it would store 4 records per every original record up in Records table.

In a nutshell the problem is to continue with this multiple references and cope with it when joining the tables or look for a different approach, whatever that is.

  • 1
    Are you asking for a subjective opinion about which design is better? Personally I prefer the one where the table is simply (RecordID, ActionID). That way, the joins are simpler, and when you add or remove an action, the schema doesn't have to change. – Aaron Bertrand Jan 20 '16 at 19:46
  • @AaronBertrand an insight in database design, yes. I'm just having trouble with the alternative being delete 3 fields, edit one and create 2 tables. It just seems... weird. But it also makes sense to have the ActionGroups table for the reason you mention, its more future-proof that the original version. – Nelz Jan 20 '16 at 19:55
  • 2
    What is the best approach depends on what scenario you are trying to implement. Your current design states that a record can have 0-4 actions and the same action may occur more than once. If that is what you are trying to implement I would choose create table RecordAction ( RecID int not null, ActionId int not null, ActionNr smallint not null, check (ActionNr between 1 and 4), primary key (RecID, ActionId, ActionNr) ) – Lennart Jan 20 '16 at 20:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.