Primary/Uniqueness Problem

[Munu via SQLMonster.com]

Hi All
I am going to design hospital database
I have a patient table tblpatient(patientid pk,name,age,................)
another table that is tblyearly(yearlyid ,patientid fk,isadmit,department,....
)
another table is tblxray(sr pk,yearlyid fk,..........,..........,.........)
another table is tblurulogy(sr pk,yearlyid fk, .......,........,........)
patientid is unique in patient table and yearlyid must be unique for a year
eg for year 2010 yearlyid starts from 1 and ending at something
and at 1st january 2011 yearly id must start from 1 and ending at something
so that previous record of patient
and form tblxray or tblurulogy patients previous record also needed
ho i design

suppose in yearly table yearlyid starts 1,2,3,4.........and ends at
something( at year 2010)
and in yearly table yearlyid starts 1,2,3,4........and ends at
something ( st year 2011)
if any body updates,or selects(loads yearly id based patient record in grid)
previous yearly id's record that is attached with another patient will be
loaded into grid .
and in case update
update tblxray set bodyregion='upper leg' where yearly id=4
then previous yearlyid data will also be updated

Can any body help me
I would REALLY appreciate any advice
cheers
Noor Anwar

--
Message posted via http://www.sqlmonster.com

[Munu via SQLMonster.com]

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-mseq/201008/1

[Hugo Kornelis]

On Wed, 01 Sep 2010 04:11:37 GMT, Munu via SQLMonster.com wrote:

(snip)

>Can any body help me
>I would REALLY appreciate any advice
>cheers
>Noor Anwar

Hi Noor,

It's hard to extract what you have and what you need from your post. But
here is one thing I see. If you want to restart numbering in tblyearly
at 1 at the beginning of each year, then you get a primary key of not
one but two columns: yearlyid and year. After all, yearlyid 3 of 2010 is
not the same "thing" (I have no idea what entity tblyearly represents -
try to use more descriptive table name!) as yearlyid 3 of 2011.

In the tables that have a foreign key to tblyearly, you'll also have to
include both the year and the yearlyid to identify exactly what row you
want to reference.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis