CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-database
thilemann
thilemann

Reputation: 397

Denormalizing and adding multiple ids to one row

I'm currently working on a SQL Server relational database which will be accessed through the Entity Framework. I have a couple of questions that I cannot seem to find proper answers to.

Please see my database model diagram:

enter image description here

1. I'm not quite sure if the ProblemType implementation is the right way to go. As it has been set up now, both columns allow null values in ProblemType since it will either contain a ProblemTypeRoadID or a ProblemTypeVandalismID. So the question is really if it is possible to de-normalize the model by removing the ProblemType table and relate both ProblemTypeRoad and ProblemTypeVandalism in the ProblemDesc table?

2. Looking at the Problem table you will find a column named HistoryIDs. This column should contain a comma-separated string with all the HistoryIDs related to a Problem. A HistoryID is essentially a ProblemDescID (since a ProblemDesc gets its IsHistory column set to true whenever it is updated) as the ProblemDesc is copied whenever changed. My question is: what is the best way to add multiple ProblemDescIDs to the HistoryIDs column? If it was something like "1,5,7" it would be easy to find all the histories for a given ProblemDesc.

3. Any other suggestions for the diagram is also very welcome :)

Thanks in advance!

EDIT: Please consider the revised diagram:

enter image description here

Now that you have given me some great suggestions for improvement, I have tried to implement most of your suggestions. ProblemType has been removed and ProblemHistory added. Changes have also been made to how WorkerComment and Media relate to their parent tables. Would this implementation work?

Thanks!

Upvotes: 1

Views: 923

Answers (1)

podiluska
podiluska

Reputation: 51494

  1. You can remove the problemtype table, and just relate problemtyperoad and problemtypevandalism to the problemtype table with the problem type id

  2. No, it shouldn't. You should have a table ProblemHistory with columns ProblemID and HistoryID with a row for each History.

  3. It seems you can have a worker comment that relates to multiple problems. Should it be the other way around? Similarly for media and ProblemDescs. Can only one worker work on a problem? Also, MSSQL has a geography type that you can use instead of CoordinateLat and CoordinateLng.

Upvotes: 3

Related Questions