SQL Primary Key for 2 tables
I have two tables (Company and Persons) in my reservation system, (These are the TYPES OF CLIENT that I have).
COMPANY TABLE
-CompanyID
**-CompanyName**
-Fname
-Lname
-Street
-City
-ContactNo
PERSONS TABLE
-PersonID
-Fname
-Lname
-Street
-City
-ContactNo
Their only difference is the COMP开发者_如何学PythonANYNAME.
Then each transaction can either be a COMPANY or PERSON..
Reservation Table
-ReservationNo
-ClientID
How can I insert a value for ClientID?
You can add one more column in reservation table specifying client type (company/person). Then ClienID can be PersonID/CompanyID. Doing a join for finding whether a clientID corresponds to person or company is not a good design
If these two possibilities are likely to never change, then having a pair of nullable columns (with appropriate foreign keys) would probably suffice:
CREATE TABLE Reservations (
ReservationNo int not null,
PersonID int null,
CompanyID int null,
/* Other columns */
constraint CK_Reservations_PersonOrCompany CHECK
((CompanyID is null or PersonID is null) and COALESCE(CompanyID,PersonID) is not null),
/* Other constraints */
)
If there are likely to be more clients, then introduce a client table:
CREATE TABLE Clients (
ClientID int not null,
ClientType varchar(10) not null,
/* Common columns for all client types */
constraint PK_Clients PRIMARY KEY (ClientID),
constraint UQ_Client_TypeCheck UNIQUE (ClientID,ClientType),
constraint CK_Client_ClientTypes CHECK
(ClientType in ('PERSON','COMPANY')) --Add more types later
/* Other constraints */
)
Then, in each client type table, enforce the correct client type:
CREATE TABLE Persons (
ClientID int not null,
ClientType as CONVERT(varchar(10),'PERSON'),
/* Columns unique to person client types */
constraint PK_Persons PRIMARY KEY (ClientID),
constraint FK_Persons_Clients FOREIGN KEY (ClientID) references Clients,
constraint FK_Person_ClientTypeCheck FOREIGN KEY (ClientID,ClientType)
references Clients (ClientID,ClientType)
/* Other constraints */
)
And repeat for the other tables, as appropriate.
You should merge "COMPANY TABLE" and "PERSONS TABLE" to "CLIENTS TABLE" :)
-ClientID
-Fname
-Lname
-Street
-City
-ContactNo
and create "COMPANY TABLE" with
-CompanyID
**-CompanyName**
-ClientID
and create "PERSONS TABLE" with
-PersonID
-ClientID
OR
you merge "COMPANY TABLE" and "PERSONS TABLE" to "CLIENTS TABLE" with NULL values like this
-ClientID
-PersonID (can be NULL)
-CompanyID (can be NULL)
**-CompanyName** (can be NULL or empty)
-Fname
-Lname
-Street
-City
-ContactNo
If you don't need mapping from PersonID/CompanyID for backward compatibility with other tables then you should drop the PersonID/CompanyID columns (and the whole "PERSONS TABLE" for the first case i mentioned). You can afterwards identify your company rows with "CompanyName NOT IS NULL".
If you don't want to merge tables consider "Damien_The_Unbeliever"s answer.
But i think you should definitely merge these two "person tables".
精彩评论