question about column that will link 2 tables.

I realize this a Transact SQL forum. If there is another forum more appropriate, please let me know.

I want to think about the best way to begin to set up this database with respect to the column that will link these tables together.

The info below is what has been given to me as my client's list of values/info she would like to include.

Here is my first pass, knowing that the ID columns need to be fixed. Thank you!

http://www.hazzsoftwaresolutions.net/db_diag.htm

Patients

ID

First Name

Last Name

DOB

1234-12

Joe

Smith

3/1/1960

5432-30

Bob

Jones

1/3/1991

3232-22

Paul

White

5/12/1982

PatientVisits

AutoID

PatientID

VisitDate

WeightPounds

HeightInches

1

1234-12

10/11/2001

180

68.5

2

1234-12

2/1/2003

185

68.7

3

5432-30

4/5/2000

155

63.0

4

5432-30

11/6/2000

165

63.0

5

5432-30

5/12/2001

164

63.5

6

3232-22

1/17/2002

220

75.0

The primary key of PatientVisits table can be set (PatientID,VisitDate) because i think a pacient have a single visit on a day; you can renounce at AutoID that is redundant.The link between the 2 tables is : Patients(ID) <>PatientVisits(PatientID)|||

Thank you Gigi.

Yes, I guess the PatientID is set by an administrator rather than by the sytem. There would be no automatically generated new Patient created by 'the database system.; I don't know how the hyphen '-' would work.

Good point about the visits per day. I would think that in some settings, there might be more than one visit per day by patients...so it looks like it would need have and autoincremented identity property.

Greg