Sample Header Ad - 728x90

Is this an abuse of UniqueIdentifiers?

0 votes
0 answers
43 views
I'm designing a new Sql Server database. Every major table uses a UniqueIdentifier as a primary key. I'm adding in a new data object called "Notebook" which is a universal object that can be attached to rows from any of several major tables. It will be used to hold user notes, user created reminders, and TO DO items in a universal format so that all objects will get the full, consistent functionality of a "Notebook" across our entire application. Each "NotebookEntry" will be tied to one or more items from the major tables, and often tied to objects in multiple major tables. (E.G. An entry that reminds EmployeeB to Check on Policy C for customer A would be tied to "Customer A", "Employee B" and "Policy C") Since UniqueIdentifiers are (theoretically) globally unique, I am considering a Many-to-Many join table with two columns "NotebookId" and "NotebookEntryId" where the "NotebookId" would be the UniqueIdentifier of it's origin row *even though the ids that that columns refers to would be scattered across multiple tables.* I'm torn between believing this is either a great idea or a horrible idea. I'd love to hear opinions on what problems might be caused by a column that is "foreign keyed" to multiple tables. The obvious: - I won't be able to use a standard SQL foreign key, although *I think* performance shouldn't suffer much as long as the queries always start from the major table and not from the Join table (IOW, Checking what notebooks are related to "Employee" objects rather than trying to write queries for every notebook regardless of what it is tied to) - I'm not sure if UniqueIdentifiers could possible repeat across tables, which would break things, but since the chance of two randomly generated UniqueIdentifiers being the same is staggeringly small, I don't think we have to worry about this too much. Plus, if two items did happen to share a UniqueIdentifiers and the entries for one showed up on the other, there would be no serious consequences.
Asked by SvdSinner (101 rep)
Jun 26, 2023, 07:28 PM