Relationship in SQL Server 2005?

Question

My situation is i have four tables Patient, Receipt, Clinic and Laboratory.

 CREATE DATABASE TestHosp

 GO
 USE TestHosp
 GO

Information about patient

 CREATE TABLE Patient
 (Id INT Not NULL PRIMARY KEY IDENTITY(1,1),
 FirestName VARCHAR(12) NOT NULL,
 LastName VARCHAR(12) NOT NULL,
 Birthday DATETIME)

 CREATE TABLE Clinic
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ClinicName VARCHAR(20) UNIQUE,
Price DECIMAL(8,2))

CREATE TABLE Laboratory
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
LaboratoryName VARCHAR(20) UNIQUE,
Price DECIMAL (8,2))

Note that the receipt contains a receipt number, One receipt belongs to one laboratory, One receipt belongs to one clinic i want do relation without create two column FK_Laboratory_ID and FK_Clinic_ID and one number for all receipt any way to do this.

CREATE TABLE Receipt
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
PatientID INT NOT NULL 
CONSTRAINT [FK_Patient_ID] FOREIGN KEY (PatientID) REFERENCES Patient(Id),
DATE DATETIME,
Paid DECIMAL(8,2))

Answer 1

As far as I have understood your model, it is one-to-many. One receipt belongs to one laborary or clinic, but clinic can have more receipt. So it can be made similary as patients foreign key...

CREATE TABLE Receipt
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
PatientID INT NOT NULL 
CONSTRAINT [FK_Patient_ID] FOREIGN KEY (PatientID) REFERENCES Patient(Id),
ClinicID INT NOT NULL 
CONSTRAINT [FK_Clinic_ID] FOREIGN KEY (ClinicID) REFERENCES Clinic(Id),
LaboratoryID INT NOT NULL 
CONSTRAINT [FK_Laboratory_ID] FOREIGN KEY (LaboratoryID) REFERENCES Laboratory(Id),
DATE DATETIME,
Paid DECIMAL(8,2))

Answer 2

Your database design does not look good at all. If you could elaborate on design requirements, your schema can be very well improved.

To answer your question,

Patient (Id, FirstName, LastName, Birthday)

Clinic (Id, Name, Price)

Laboratory (Id, Name, Price)

Split your Receipt table into,

Clinic_Receipt (Id, PatientId, ClinicId)

Laboratory_Receipt (Id, PatientId, LaboratoryId)

     //Create Unique Ids for Receipts

     public int CreatId()
     {
         TimeSpan t = (DateTime.UtcNow - new DateTime(1970, 1, 1));

         return (int) t.TotalSeconds;
     }

Answer 3

Here is another option (I did't edit my previous answer so that votes can be assigned to a better solution for you).

You can create a generalization as follows. However you will need more JOINs. There will be alwas 1 Receipt row and then 1 Receipt_Laboratory or 1 Receipt_Clinic with a corrensponding reference to either Laboratory or Clinic.

CREATE TABLE Receipt
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
PatientID INT NOT NULL 
CONSTRAINT [FK_Patient_ID] FOREIGN KEY (PatientID) REFERENCES Patient(Id),
DATE DATETIME,
Paid DECIMAL(8,2))

CREATE TABLE Receipt_Laboratory
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
LaboratoryID INT NOT NULL 
CONSTRAINT [FK_Laboratory_ID] FOREIGN KEY (LaboratoryID) REFERENCES Laboratory(Id),
ReceiptID INT NOT NULL 
CONSTRAINT [FK_Receipt_ID] FOREIGN KEY (ReceiptID) REFERENCES Receipt(Id)
)

CREATE TABLE Receipt_Clinic
(Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
ClinicID INT NOT NULL 
CONSTRAINT [FK_Clinic_ID] FOREIGN KEY (ClinicID) REFERENCES Clinic(Id),
ReceiptID INT NOT NULL 
CONSTRAINT [FK_Receipt_ID] FOREIGN KEY (ReceiptID) REFERENCES Receipt(Id))