Sql-server – Finding a good DB design for the project

database-designinheritancereferential-integritysql server

I've posted this on stackOverflow but this might be a more suitable place.

I'm really struggling to find a good DB design for my project using SQL server.

I've already implemented a few models which worked great till this point, but now that I need to add something extra I just can't find a good option and I'm stuck with it ATM.

I've supplied below 2 very simplified models (class diagrams) I've tried, but both models are not working well.

1st model: which I also prefer if it's possible to fix

1st model

I should explain first that msg and action can have the same basic id (i.e 1) but when used with tabID or groupID as a composite primary key, it becomes unique.

Here you can see that UserInput is created using only the basic ID which creates a problem to save both a Msg and Action with id 1 for example.
Is there anyway around this? maybe a way to say that ya Action and Msg are extending UserInput but they define all the keys themselves?

2nd model:

class diagram

Each Critical Point is related to either a Msg or Action, but how can I define it since they have a different set of PK? I would like to keep referral integrity.

I would REALLY appreciate help on this issue.

Update

Maybe my only option is to use this?

UserInput
  UserInputID:   PK (autogenerated)

Action
  ActionID:      PK
  TabID:         PK, FK
  UserInputID:   FK

Message
  MessageID      PK
  GroupID:       PK, FK
  UserInputID:   FK

The question is can I make the Msg and Action extend from UserInput without taking defining his PK as part of their PK…

Best Answer

In the comments you mention that a UserInput is either a Msg or an Action and not related to many actions or to many messages. The first model does not accurately describe this relationship.

You correctly identify that the problem is how to make Action and Msg "extend" UserInput. This is often called Supertype/Subtype pattern in database design.

I would make (UserInputID) as the PRIMARY KEY in both Action and Msg:

UserInput
  UserInputID   PK

Action
  UserInputID   PK, FK1
  TabID         FK2

Message
  UserInputID   PK, FK1
  GroupID       FK2

The rest of the tables would stay unchanged. This is the easiest thing to do, assuming that you have the freedom to define how the IDs are populated (for every new Action, a new UnserInput is inserted and the ID is used for the Action, too. Similarly for the Msg table.)
It still does not address all potential problems. A malicious or wrong update can have the database into some weird status, where a UserInput is both an Action and a Msg.

An altered design can overcome the issue by adding a Type column and some constraints:

UserInput
  UserInputType  PK
  UserInputID    PK
  CHECK (UserInputType IN ('A', 'M'))

Action
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  TabID          FK2
  CHECK (UserInputType = 'A')

Message
  UserInputType  PK, FK1
  UserInputID    PK, FK1
  GroupID:       FK2
  CHECK (UserInputType = 'M')

CriticalPoint
  UserInputType  PK, FK1
  UserInputID    PK, FK1
Related Question