Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

design database Expand / Collapse
Author
Message
Posted Thursday, October 25, 2012 7:55 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, February 21, 2013 12:01 PM
Points: 149, Visits: 346
i am desiging database and i need answer.

for example i am getting flat file ,which contains pharmacy into and patient info

pharmacy :

pharmacyid primary key
name

patient:

patientid primary key
firstname
lastname

now they dont have any relation between them,
i can have multiple record of patients from same pharmacy, that i need to store, so if there is pharmacyid is primary key, how can i store tha record.

similarly i can have multiple records for same patient from different pharmacy.

what should i do

please help me with design, what kind of relation i need to make,do i need to create third table


Post #1376997
Posted Thursday, October 25, 2012 8:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 15,628, Visits: 28,011
The basic design is quite simple. You have to create an interim table that has the primary key from patient and the primary key from pharmacy. That creates the many to many join that you need.

Unfortunately, depending on how the data is coming from your pharmacy, loading this could be tricky. Are the primary keys for the patient unique across pharmacies? Or is it only unique within a pharmacy? If it's only unique within a pharmacy, then you have to have some other method of identifying patients. If all you have is first and last name, you're in trouble. There can be more than 'Li Wong' so just relying on the name is going to hurt you. With that other unique constraint you can just look up the primary key from the person as you perform the loads and put them into the interim table.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1377042
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse