Primary Key field design for multiple locations

[TerryMc]

I am designing an app with SQL backend and .Net front to replace an out of date Access app our laboratory company uses to more or less run our company. I have a quite a bit of experience with Access but am a SQLServer newbie

The Access 97 app which I ‘inherited’ uses a PK known as “WorkOrder” which served a number of functions in the old app. Our company had one location when the app was built so a simple increment key worked fine. Samples we test need to be retained and are filed by this Workorder field, and it is critical that each sample be uniquely identified. But once we added locations the fun began.

The access app had the key modified to contain a ‘location’ code and concatenated sample number. Each location has its own code and I had to create a table that kept track of the last sample number used at each location, which must be separate so samples can be filed at each location. Then code put them together to create a string PK unique to each location with a number portion sequential in that location. It looks something like this

A0001
A0002
A0003
B0010
B0011

I know this design is horrible but the program, which I was modifying, had dozens of queries, reports, and tons of undocumented code that relied on this “WorkOrder” field and with the time I had this was the only option.

Now that I am rebuilding I want to do it right. I had two thoughts on how to go with this in the new DB.

My first though was to create a compound primary key, with one field for the location code and one field for the sample number like below.

MainTbl
intLocationID (PK)
intLocationSeq(PK)
Field 1…

LocationTbl
intLocationID (PK)
intLastNumberUsed

This still does not get me away from needing code to add the MainTbl additions and uses the actual PK values for user observable data.

The second is to have an auto increment PK which has no meaning to the users or reports etc, and create a separate table to keep track of this.
(by auto I mean IDENTITY int)

MainTbl
autoID (PK)
Field 1…

PointerTbl
autoID2 (PK)
autoID (FK)
intLocationID
intSampleID


LocationTbl
intLocationID (PK)
intLastNumberUsed


Any advise or comments I can get on which way to go would be appreciated.

Terry

 

[Patrick2002]

Take a look at the relationships in the Pubs database (make a diagram adding all user tables, that's easier). You'll find that your problem somewhat ressembles the relationship between the tables titles, stores and sales. You'll find that your own second solution is in the right direction, but your primary key in your table pointertbl should consist of the two foreign keys coming from your maintbl and locationtbl.
Of course, you must remember that when all is done, you need to migrate your data from your access DB to the new SQL version. Whatever schema you make, keep this in mind.
Succes ! B-)

 

[TerryMc]

Thanks Patrick

I guess one of the things I am wrestling with is what seem to be two schools of thought on Primary Keys.

The “Autonumber” camp assume that every table should use some form of autonumber as the PK and this should be the FK in other tables.
The “Use Data” camp thinks PKs should be implemented on tables based on combined columns of data.

I have used both an none of my DB’s have been strictly one or the other. Both have advantages and disadvantages and I have problems resolving which way to go.

Terry