February 20, 2024 at 9:45 am
We have a fixed number of entities (each with their own table) such as Person, Service, Organisation, Contract, Invoice, etc. We also have a table for Notes - this has quite a few columns in order to support existing functionality. Each instance of an entity (e.g. a Person or a Service etc.) can have any number of Notes. Mercifully, each specific Note can only relate to one instance of an entity - a given Note can only be referenced by a single Person, Service, etc. Currently, we have implemented this via two columns on the Note table: EntityID - the PK value of a row in the Person, Service, Organisation, etc. table. ContentTypeID - 1 for a Person, 2 for a Service, 3 for an Organisation, etc. The main problem with this approach is the inability to establish a FK from EntityID to the Person, Service, Organisation, etc. tables. There might also be a performance implication too. The problem with an alternative is that we have over 30 tables in the database that need to have Notes. My understanding is that there are two options for refactoring: Add 30+ nullable columns to the Note table; PersonID, ServiceID, OrganisationID, etc. These could be sparse and we could add indexes for only the non-null values, plus FKs to the Person, Service etc. tables. But that's a lot of columns! Add an "Entity Note" table for all the entities needing a Note. e.g. Person_Note, Service_Note, Organisation_Note, etc. These would simply contain pairs of PersonID and NoteID values (or ServiceID and NoteID etc.) But that's a lot of tables! I'm starting to think we should just stick with the existing EntityID / ContentTypeID approach and hold our noses whenever we come across it. But if you know of a better approach, I'd be very interested to hear it. tunnel rush
Some additional details:
Can anyone help me with this issue?
February 20, 2024 at 11:52 am
You may find this article an interesting read … it is relevant to your question. Not that it solves anything!
https://www.sqlservercentral.com/articles/antipathy-for-entity-attribute-value-data-models
Based on your description, it sounds like you may already have the most practical solution architecture in place.
When the Notes table is queried, do those queries span multiple entities (eg, show me all notes from date x)?
If you are having data integrity and query performance issues with the existing solution, people here will likely be able to help you optimise things to improve the situation.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 20, 2024 at 2:07 pm
I'd rather strongly recommend a separate note table for each entity.
If you need/want a single querying point, create a view that unions those individual tables. You can use an EntityType/EntityTypeID column in each table to allow filtering for specific entities when needed.
February 20, 2024 at 2:56 pm
You can't use declared RI, but you could write your own.
As to the design, I assume the EntityIDs of the various tables -- Person, Service, Organisation, etc. -- overlap. That's not best: really you should use a SEQUENCE, for example, to make sure each EntityID is unique, even across tables. Of course that could be very difficult to implement/refactor in now.
The base setup is similar to a supertype / subtype in data modeling, like sys.objects vs sys.tables, sys.procedures, etc., for MS SQL Server itself. I'd stick with that type of modeling.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply