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.
  • 1
    "but since the chance of two randomly generated UniqueIdentifiers being the same is staggeringly small" - FWIW, collisions are actually fairly commonly possible (depending on the number of rows we're talking). I've had it happen multiple times in the past. It's important to proactively have logic to verify for collisions upon generation and regenerate when such occurs, if you're using UNIQUEIDENTIFIERs (or any other sort of GUID object).
    – J.D.
    Jun 27, 2023 at 3:30
  • 1
    Instead of writing so lengthy post,you should so few table deign and how Notebook is relted to few tables.Also clearly point out your main concern.UniqueIdentifiers duplicity or UniqueIdentifiers as primary key.See no body has responded your query.Please rephrase your question
    – KumarHarsh
    Jun 27, 2023 at 5:38
  • A uniqueidentifier is not terrible as a primary key. But if it is also the clustered index you are in for a long and bumpy ride. There are plenty of great articles on this topic. Here is one of the best ones. sqlskills.com/blogs/kimberly/…
    – Sean Lange
    Jun 27, 2023 at 13:27


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.