Composite PKs

[rodmc]

Hi Folks

Just a quick question about linking composite keys

for my composite PK I have autonumber and a static txt value, so my question is do I need to create both of these fields as FKs in the other table and link both fields via the same method as a normal 1-many relationship?

 

[llkhoutx]

The autonumber field is always unique, but not necessarily sequential. A long interger foreign key will accurately link to the related table.

I use PK as a prefix for the primary table autonumber field and FKTable with the related table, named "Table". I never have confusion is writing SQL statements.

 

[boblarson]

Me, I would just use an autonumber PK (surrogate key) and create a multi-field index set on no duplicates (see here for how) to avoid duplicates. Then you only need to store one value as a foreign key.

 

[gemma-the-husky]

in case this isn't clear -- you say your PK is an autonumber plus some other data.

the point is that the autonumber on its own is sufficient for a PK, and to relate this table to other tables, as the FK

now if the text data is unique for each record, then by all means add ANOTHER key to the table in respect of this. The key may still be useful even if this data is not unique.

 

[Dhamdard]

Yup. If I understand you correctly, you can go with your composite key and link it to the sub table as 'One To Many' link - in case only if the sub table is a child table. If not a child table, you can link them as 'One To One' only. My experience is that making the PK a composite one saves time and gives you more info rather than just using auto-number as a PK. However, it depends from situation to situation.

 

[rodmc]

Cheer guys I will give it a go

The reason for prefixing is I have 4 tables which are similar which are related to one single table, the 4 tables use autonumber as the PK (users have a habit of screwing up the PK if I dont use autonumber) so I want to differentiate on the single table which one of the 4 tables the record came from.
If you get my meaning that is!

 

[gemma-the-husky]

whoa!

how do users mess up your PKs.

When does a user see a PK?

what is a user doing with PKs?

your description makes it sound like your data structure could be imporoved, to be honest. Can you share your table layout?

 

[nanscombe]

It sounds rather like a common table being used as a child for several parent tables.

Something like:
tblSales (ID [autonumber PK] + TableType [value='s'])-> tblComments (ParentID [Long Integer FK] + ParentTableType)

tblCustomers -> tblComments
tblCustomers (ID [autonumber PK] + TableType [value='c'])-> tblComments (ParentID [Long Integer FK] + ParentTableType)

tblProducts -> tblComments
tblProducts (ID [autonumber PK] + TableType [value='p'])-> tblComments (ParentID [Long Integer FK] + ParentTableType)

The table tblComments would then hold comments for all three tables with the type being used to differentiate which type they were.

The ID and TableType fields would create a composite Primary Key which would link to ParentId and ParentTableType would be the composite Foreign Key.

The tblComments table could then hold records like:

ID, ParentId, ParentTableType, Comment
1, 100, "s", "Sales comment 1"
2, 327, "c", "Customer comment 1"
3, 1234, "p", "Product comment 1"

Does that sound anything like it rodmc?

 

[rodmc]

nanscombes pretty much spot on

the system is based on 4 paper forms which are very similar but not quite the same, they all have elements in common (obviously). For this particular example the 4 forms have a physical examination table which are exactly the same on all forms, but when the person completes the record I want to know from the tblPhysExam table what form it relates to. I cant use autonumber on its own as Ive used autonumber as the PK in the subtables and this would create duplicates on tblphysExam hence the need to create a composite PK which include a prefix for the autonumber.

In response to your question Dave, in certain circumstances I am asked to use a particular data item as the PK. This item is called a CHI number and is used by the NHS and every person in Scotland has a unique one, so it makes an ideal candidate for a PK, but as always end users somehow screw it up. Ive tried in the past to get authorisation to allow my system to connect to the national system to verify the CHI number but due to data protection issues I am not permitted.

 

[nanscombe]

If it's like my comments example then I would say that you would need both fields stored on the child table.

For example, the tables would be linked by ID (autonumber) and Type (single character would suffice) on the parent table linking to ParentId (Long Integer) and ParentType (single character would suffice) on the child table.

It would probably be sufficient to have the Parent's ID as the Primary Key.

Although I don't know whether having them both as a composite Primary Key would give a performance advantage?

Each of the fields would probably benefit from indexing though.

 

[gemma-the-husky]

nanscombes pretty much spot on

the system is based on 4 paper forms which are very similar but not quite the same, they all have elements in common (obviously). For this particular example the 4 forms have a physical examination table which are exactly the same on all forms, but when the person completes the record I want to know from the tblPhysExam table what form it relates to. I cant use autonumber on its own as Ive used autonumber as the PK in the subtables and this would create duplicates on tblphysExam hence the need to create a composite PK which include a prefix for the autonumber.

In response to your question Dave, in certain circumstances I am asked to use a particular data item as the PK. This item is called a CHI number and is used by the NHS and every person in Scotland has a unique one, so it makes an ideal candidate for a PK, but as always end users somehow screw it up. Ive tried in the past to get authorisation to allow my system to connect to the national system to verify the CHI number but due to data protection issues I am not permitted.

I think you are misundersatanding things ... having four tables all with autonumbers does not mean that the autonumber should be the same, or match for related items. How can it? If it does, it implies a 1-1 realtion, and if you have a 1-1 relation, you don;t NEED separare tables

with regard to "I am asked to use a particular data item as PK". Tell them you can't, and it won't work. Why are users telling designers how to design? I take it you are saying users enter this item of data - you then use it in other tables, and then find it needs changing.

And why only "certain circumstances", anyway?

Seriously - without checking in detail, your table design just sounds wrong. Use an autonumber key, and include the CHI reference as another bit of data. Then if it needs changing, you can change it.

But you need to resolve the data structure issues. Presentation (on 4 forms) certainly does not indicate or mandate that you need to have 4 tables corresponding to these 4 forms.

Your problem sounds to me that you are trying to have 4 almost identical tables managing similar sets of data.

 

[nanscombe]

Having something like ID (autonumber) as the Primary Key for each table probably makes more sense from a development point of view.

The CHI number may uniquely identify a person but unless you can guarantee it's correct, or even available, it would not be effective.

From what rodmc is saying the examination child table structure is common to all 4 scenarios, whereas there is no guarantee that the parent tables share the same structure.


However, if the data for entry were similar enough (3 or 4 fields being different) you could probably get away with the same underlying table with several different entry forms to make it easier for the people entering the data.

The labels on the entry forms may be different but the underlying data may be the same.

 

[rodmc]

Dave, unfortunately that decision comes at a higher level (as regards to having the CHI as PK) Board wide system all use this CHI No hence the reason why any of the smaller bespoke systems such as the one I am building are forced to use it as a PK. Corporate policy you might say (therein lies the problem when working for a govt agency such as the NHS)

The data in the forms is similar but not the same. In this case it relates to childrens/young persons health. The data fields like I said have slight variations, they may look the same at first glance but when you drill down you can see that they are indeed different. At first I though peice of cake until like I said, I went into it in greater detail.

TBH Im regretting saying that I would do it.

 

[rodmc]

Right, bugger it, going to revisit the tables as suggested!!!!!

When the big bosses come knocking Im just gonna say it was Dave that done it

 

[nanscombe]

(With all appropriate disclaimers)

Users see what goes into a system and what comes out but do they know what's really going on under the bonnet?

A Black Box system comes to mind.

I mean .. as long as you can find a record by entering the CHI?

Or are the NHS really laying down data standards for the data tables in their systems?

Come to think of it, as an ex (English) Civil Servant, that wouldn't surprise me.

 

[rodmc]

you know where Im coming from then, lot of rules and regs that make sense to no one, not even god

 

[boblarson]

I look at it this way. Users THINK they know what a PK is. So, sometimes rules get in the way of database design. But, the PK is only really needed by the system, so I let the system do system stuff and keep the users out of the system stuff and let them have their little numbers the way they want them in any situation they want to see them. But they don't have to know that the system isn't using that to maintain itself.

I've had the situation where I actually had to get up the nerve to say (and this wasn't easy) - "do I tell you how to do your job? Are you a database developer? Do you want a system that is accurate and easy to use, or do you want one that is created with a bunch of potential problems? I take great pride in my work and I really feel like you are telling me to return substandard work when it isn't necessary to do substandard work. If you want me to do substandard work, then I am going to have to work at getting another job somewhere else where I can do the best I can at what I do. Should I start looking?

(by the way, they caved, but I had to be ready to be unemployed to stick up to my principles)

 

[nanscombe]

That's the thing, just because there is a field that is unique to a record doesn't mean that it should automatically be the Primary Key.

Social Security (NNN-NN-NNNN 11 characters) / National Insurance (AANNNNNNA 9 characters) number can certainly be used as the unique identifier on a person table but it is not practical elsewhere in the data structure.

11 bytes (US SSN) vs 9 bytes (UK NI) vs 4 bytes (autonumber) per record.

 

[gemma-the-husky]

Dave, unfortunately that decision comes at a higher level (as regards to having the CHI as PK) Board wide system all use this CHI No hence the reason why any of the smaller bespoke systems such as the one I am building are forced to use it as a PK. Corporate policy you might say (therein lies the problem when working for a govt agency such as the NHS)

The data in the forms is similar but not the same. In this case it relates to childrens/young persons health. The data fields like I said have slight variations, they may look the same at first glance but when you drill down you can see that they are indeed different. At first I though peice of cake until like I said, I went into it in greater detail.

TBH Im regretting saying that I would do it.

Using the CHI is not a problem - providing

a) it is available for entry of a new client and
b) it doesn't change, preferably

it still means you are using a string as a PK, which is less efficient than a number.

if it is not available for every new client, then you are stumped anyway. How do you get round that one, or does it not happen.

 

[rodmc]

I think the reason for the CHI is this, we have a corporate policy that states 100% CHI compliance, by this they mean no record should be recorded unless the CHI is known and used, no record should be without the CHI.
Thing is, this wouldnt be a problem if I could authorise the CHI via the national system.

CHI numbers are like NI numbers (no idea if NI numbers are assigned at birth but CHI's are) once youve got it it never changes (part based on DoB).

Anyway, that kind of leads me to another question, the CHI number is a number but I usually store as text (I dont need to do any calcs of any sort on it), anything wrong with that?