CODEWITHSUNDEEP
sqlsql-serverdatabaserelational-database
mister_giga
mister_giga

Reputation: 612

SQL tables, two table to reference one column in thirds table

enter image description here

I have two kinds of users : Person and Company.

I want to save their password in one table.

How can I connect these two tables with Password? Using sql server (MSSQL)?

Or maybe there are other ways to do it?

I dont want to keep hashedPass in Person or Company tables because I want keep any password changes that user have made.

Upvotes: 0

Views: 524

Answers (5)

Charles Bretana
Charles Bretana

Reputation: 146499

Make a superclass table for all users, and put the password in there (as well as any other attributes that are shared among all users). Then make the Person and Company tables subclasses of the user table.

EDIT: if you need a third type of user, (say Applications - for software applications that need to logon), add another table for that user type as a third subclass.

enter image description here

Upvotes: 1

Mark Adelsberger
Mark Adelsberger

Reputation: 45649

Well, this is probably not the only solution, but I'd look at creating a USER table with a USER_ID and whatever columns are common to PERSON and COMPANY; then treat the PERSON table and the COMPANY table as extension tables of USER. This solves several problems:

1) You can create a simple FK from the PASSWORD table to the USER table 2) You don't have to worry about somehow a COMPANY record and a PERSON record duplicating an ID value

Upvotes: 1

JohnHC
JohnHC

Reputation: 11195

Add another table: 

Entity
------
id
Entitytype [company/person]
ExternalID [either person.id or company.id]

Link Password.user_id to Entity.id

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I think you are going in the wrong direction. Put a PasswordId in each table where you want a password:

create table Person (
    . . .
    PasswordId int references Passwords(PasswordId)
);

. . .

create table Passwords (
    PasswordId int identity(1, 1) primary key,
    . . .
);

Then remove the userId from the Passwords table.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311308

This sounds like a pretty straight forward foreign key:

ALTER TABLE person 
ADD CONSTRAINT person_fk FOREIGN KEY(id)
REFERENCES passowrd(user_id);

ALTER TABLE company
ADD CONSTRAINT company_fk FOREIGN KEY(id)
REFERENCES passowrd(user_id);

Upvotes: 0

Related Questions