0

I'm new to DB management and I'm trying to create an ER Schema Diagram to best understand how to build my database for a project.

My project is to list every speaker (i.e. an individual person) that has spoken at specific events.

However, a name is not unique, so how can I make it unique?

Of course, I could just make an AUTO-INCREMENT ID as the PK but that would almost certainly create duplicates...

Any ideas on how I should approach this?

Ideally, I want the speaker's name to be 100% unique because often these speakers speak at multiple events....

All help very much appreciated!

1 Answer 1

1

You are absolutely right that a surrogate key (ID or otherwise) won't solve your root problem which is more fundamental than the database schema - "How do you tell speakers apart in the real world?" That's a business question, and full name may or may not be good enough. You should ask yourself - If I have 2 speakers named John Doe, how do I tell them apart in the real world, and that will be your answer to the key.

Common attributes used to identify people in these kind of data universes are Email address, User name / Alias (login to your system), SSN, perhaps even name + address may be unique enough for your business needs - what are the odds of 2 people with the exact same name residing at the same address? Theoretically, you should accommodate every possible case, but in practice this would be much better than having a surrogate key, which will guarantee duplicates as you correctly observed, and not having any way to tell the entities in the database apart. Using an ID will lead to much worse data consistency problems later on when the 2 John Doe start showing up in the wrong conferences :-)

HTH

4
  • Very helpful thanks. What about using a Composite Key with a random string AND the individual's name. So, of course - John Doe EU76 and different to John Doe 6EHD. I guess then the challenge is to create an alert so when a John Doe is uploaded then there is manual check?
    – Henry
    Dec 11, 2018 at 6:28
  • A random string won't help you identify the individual, unless you tattoo it on his face :-) It's still a surrogate key, and you still have no way to tell them apart. Let's say John Doe EU76 shows up to a conference. how do you know it's him and not the 6EHD John Doe? It's the same root challenge... You won't get around it until you decide what is your real universe key.
    – SQLRaptor
    Dec 11, 2018 at 6:51
  • Aristotle law of identity states that "each thing is identical with itself. By this it is meant that each thing (be it a universal or a particular) is composed of its own unique set of characteristic qualities or features, which the ancient Greeks called its essence." = If I can't tell you apart from others, you don't exist :-)
    – SQLRaptor
    Dec 11, 2018 at 6:51
  • BTW - Kudos for thinking about this challenge. as you will see in the questions that come up often in this forum, 99% of the people would just use ID, and never look back. However, a few years down the line, when the duplicates start to significantly affect the business, they, or those who will come after them, will suffer the consequences. Unfortunately, at that point it is usually too late. I've witnessed a company go bankrupt because of a similar issue, as they had no choice but to admit to their customers that the tax reports generated from the DB could not be guaranteed to be correct...
    – SQLRaptor
    Dec 11, 2018 at 15:42

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.