What is the best database design for multiple tables with a ratio of 1 to one table?

Question

For example, I have three tables that store different types of users with different data. But now I want to keep their contact information. The table in which their contact information is stored will be suitable for storing data for all user tables.

The problem is that I have a different or possibly duplicate ID between different user tables, so it does not allow me to create a relationship with my pivot table. One solution is to create a contact table for each type of user, but this seems wasteful since the only difference is the identifier.

In addition, I was thinking about storing the contact ID in the user table, but this seems less ideal, since we may not have user contact information until it appears.

Any other options that I am missing?

Answer 1

This is what I would do:

table Users
UserID                 -PK auto number
UserLogin
UserName

table UserSpecialType1
UserSpecialType1ID     -PK auto number
UserID                 -FK
SpecialInfoA
SpecialInfoB

table UserSpecialType2
UserSpecialType2ID     -PK auto number
UserID                 -FK
SpecialInfoC
SpecialInfoD

table UserContactInfo
UserContactInfoID      -PK auto number
UserID                 -FK
EmailAddress
PhoneNumber
Address

Answer 2

On top of my head, I would put all users in the same table with a Type differential.

TABLE User
Id
Value1
Value2
UserTypeCode

TABLE UserType
TypeCode

TABLE Contact
UserId
ContactInfo

TABLE UserTypeAttribute
UserType
AttributeTypeCode

TABLE AttributeType
AttributeTypeCode

TABLE UserAttributeTypeValue
UserId
AttributeTypeCode
Value

Answer 3

You can use a compound key in the contact table, for example. UserTypeID, UserId