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
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
Action with id 1 for example.
Is there anyway around this? maybe a way to say that ya
Msg are extending
UserInput but they define all the keys themselves?
Critical Point is related to either a
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.
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
Action extend from UserInput without taking defining his PK as part of their PK...