CODEWITHSUNDEEP
sql-serverdatabase-design
Eric
Eric

Reputation: 19152

Share an "identity" across tables

I am working on a database that has an existing Individuals table that every user type derives from. In other words, there are tables like:
Individual: FirstName, LastName, Email, <lots more>
Employee: IndividualId
Customer: IndividualId
 etc.

Now, I want to add new type of user (WeirdPerson) that does not derive from Individual. (WeirdPerson has significantly less data associated with it than any Individual, and I really don't want to set practically every field in Individual to null for a WeirdPerson.)

I need a key field to use on a table that will have entries from WeirdPersons and entries from Individuals. This suggests map tables like so:
MashedupIndividuals: MashedupId, IndividualId
MashedupWeirdPerson: MashedupId, WeirdPersonId

I want MashedupId to be an auto-generated field. Since I'm using TSQL, an identity seems a good choice. Except that MashedupId is split across two tables. I considered yet another table:
MashedupIds: MashedupId
Set MashedupId to be an identity, and then make it a foreign key in MashedupIndividuals and MashedupWeirdPerson.

Is this the best way to proceed forward? How would you solve this?

EDIT: To clarify, the only piece of information I have for a WeirdPerson is an email address. I considered pulling the email field out of Individual, and then making a new GlobalPerson table with only GlobalPersonId and Email. The GlobalPerson table (or whatever better name I use) doesn't feel as natural as separating WeirdPerson as an entirely different type. However... I am willing to reconsider this position.

Upvotes: 0

Views: 682

Answers (3)

Mayo
Mayo

Reputation: 10782

I would suggest a table to host data common to all people in your application. Then you could have additional tables for specific types of people and link them back to your common table.

tblPerson

  • PersonID (pk)
  • name, address, birthday, etc.

tblEmployee

  • EmployeeID (pk)
  • PersonID (fk to tblPerson)
  • Title, OfficePhone, Email, etc.

tblCustomer

  • CustomerID (pk)
  • PersonID (fk to tblPerson)
  • Other fields...

EDIT:

Here are some definitions more applicable to your question (and also more fun with these weird people). The key is establishing the data that weird people and normal people share and then establishing the tables/relationships to support that model. It might be necessary to move fields that are not applicable to weird people from tblIndividual to tblNormalPerson.

tblIndividual

  • IndividualID (pk)
  • Other fields for data applicable to both weird/normal people

tblWeirdPerson

  • WeirdPersonID (pk)
  • IndividualID (fk to tblIndividual)
  • NumberOfHeads (applicable to weird people)

tblNormalPerson

  • NormalPersonID (pk)
  • IndividualID (fk to tblIndividual)
  • FirstName (other fields applicable to normal people)
  • LastName
  • Etc...

Upvotes: 2

recursive
recursive

Reputation: 86064

You can use a uniqueidentifier field for your id. This is guaranteed to be unique across multiple tables. Use the NEWID() function to generate new values.

Upvotes: 1

Beth
Beth

Reputation: 9607

You could have a table with three fields, one of which is always null:

MashedupId, IndividualId,WeirdPersonId

or with an ID field and ID type (individual/weird)

Upvotes: 0

Related Questions