How to use PK twice? 1

[khwaja]

I might have got the whole concept wrong but I hold the primary key as very sacrosanct as it prevents you from creating duplicate records and also ensures data integrity. But lately I have been facing difficulty in meeting user requirements which dictate that I need to have two or more instances of record appear within the database. I mightl illustrate it with example.

We have a national chain of supermarket, each denoted by a Location number. I have created a database to maintain refurbishment plan as to when a store gets refurbished. Treating LocNo as the PK has helped me avoid accidental creation of new stores. This arrangement has worked fine. However, lately I have been asked to provide an additional refurb plan for the same store which not only receives a regular refurb but also gets extension for a Liquor department. So effectively, there are twi projects running concurrently. They need to track progress on these two seperate projects at the same time using the database I havecreated.

I am finding it difficult to maintain two sets of info for the same store. Is there any alternative way to have one store appear twice? I have tried going on the path of creating a separate table with one to one relationship which was the subject of considerable discussion in this forum but I did not quite get any outcome of that. I will really welcome some insights.

Cheers

 

[Darrylles]

Hiya,

Is there a reason why you can't have a 1-many relationship between a LOCN table (containing details of the store) and a REFURB table containing details of the refurbishment?

I might have over-simplified, but I had to ask the question.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[khwaja]

Thanks Darrylle.

I am not an expert in database design but I thought that if there were not many refurbs relevant to one store, I could not have one to many relationship. Please correct me if I am wring but you are saying that one could have a one to many relationship even if instances of record on many side may occur once? If this the case, then I am sure I can create a refurb table to show many relationship of each store where we may also decide to add a liquor store. I wait for your cofirmation.

Cheers

 

[LittleSmudge]

A 1-to-Many relationship means you have the OPTION of many.


If you step back and think about it - you can take the most complex database design, and remove all of the data and all of the 1-to-many relationships become none-to-none relationships - but the design is still intact.

You should have something like a
tblLocation being the site location table with a PK of
LocationId

and a
tblReferbishments being the table containing the refurb data
fields in tblReferbishment could include:-
LocationRef - being the FK to the tblLocation
ReferbStartDate - being the start of Referb
etc.. ..
The above two might form an effective PK pair for this table.



'ope-that-'elps.

G LS

 

[khwaja]

I am very grateful for your your detailed response and the guidance. Based on your response earlier, I started putting togather the structure accordingly - and it works.

Regards