Implementing Surrogate Keys In Access

[The Stoat]

Hi,

I don’t want this to descend into an argument over the use of natural or surrogate keys. If you put a search into Google you can find hundreds of such discussions, it’s been done to death.

I’m actually interested in the way(s) in which Access allows you to implement surrogate keys – most often in the form of auto-number fields or GUID’s – and the considerations that must take place when doing so. I think it will prove enlightening.

The following example is for illustration purposes and is deliberately kept simple.

A medical consultant runs an outpatient clinic everyday between 9am and 12pm. This table would allow us to record the Clinic information.

ClinicID GUID
ClinicSpecialtyID GUID FK from TBL_SPECIALTY i.e. Ophthalmology
ConsultantID GUID FK from TBL_CONSULTANT
ClinicDate DATE

The Business model tells us that a consultant cannot be running more than one clinic at one time.

The question is: How do you implement in Access constraints on the fields within the tables to prevent the input of duplicate data.
What I’m asking for is the specific actions and methodology i.e. if you say “ I’d add an index”, how exactly with pictures for preference , if you say “I’d do this in code with a select query” please explain the reasoning.


TS

 

[Pat Hartman]

I believe in allowing the database engine to enforce data integrity whenever possible. That means, primary keys, RI, unique indexes, data types, validation rules. Only if the database engine cannot enforce a specific rule would I write code to do it.

So, to answer your question, I use unique indexes and I would never consider code to enforce uniqueness. In the case of your particular question, I don't have enough information to make a final decision but I think, an additional table may be required to enforce the rule.

 

[The Stoat]

I believe in allowing the database engine to enforce data integrity whenever possible. That means, primary keys, RI, unique indexes, data types, validation rules. Only if the database engine cannot enforce a specific rule would I write code to do it.

So, to answer your question, I use unique indexes and I would never consider code to enforce uniqueness. In the case of your particular question, I don't have enough information to make a final decision but I think, an additional table may be required to enforce the rule.

Hi Pat

Thanks for the reply. I've done some research recently on this subject and it seemed to me that their was real lack of clarity in a lot of what i read.

For example many people who discussed implementing a design in Access appeared to be talking about methods that i've read about for SQLServer
i.e. Clustered indexes. The terminology may or may not be correct for Access but there was no real explanation about how you would actually do it.

What i hope to be able to do is put together a definitive example describing the theory and methodology for implementing data integrity in Access with associated benefits and pitfalls of different methods (which may or may not even exist ). For example you said you'd create a unique index in Access. If some one asked me to do that i would create what i call a compound key using the key symbol in the table design menu to create n key fields which give that record uniqueness and then add an Auto-number or GUID field set to index yes(no duplicates), which i would call a surrogate key. I've no idea if that is right or wrong or the descriptors are inappropriate/incorrect. It would seem to work but there may be other ways to do it. I'm sure that people on here have said they use code. I would find that inappropriate but i think it would be worth knowing why people do it and what are the percieved benefits/costs.

Thanks again

TS

 

[Pat Hartman]

Access doesn't give you options for optimization that something like Oracle or SQL Server would. Access does make a token attempt however at creating a clustered index on the primary key. It does this by rewriting each table in pk sequence each time the database is compacted. As far as I know the index isn't altered between compacting except to add records to the end. That's why compacting regularly is so important even if you are not suffering from database bloat.

I don't know why some programmers insist on writing code to enforce RI. Arrogance I guess. In large shops there is also sometimes a problem getting support from the DBAs who are the only ones actually allowed to make modifications to a database's structure.

 

[jsanders]

Pardon me for asking a side question.

Pat, you said you should compact data bases regularly? Does that include the front ends as well?

Is there a way to make them automatically compact on opening?

Thank you

 

[ByteMyzer]

It is always a good idea to compact the front-end following back-end compact. This is because a non-compacted front-end linked to a compacted back-end can sometimes result in corrupted field indexes in the table link(s). This can typically occur when a back-end has been allowed to bloat over a significant period of time, and then compacted. Also, front-ends can bloat with unused space as well.

You can set the front-end attribute to compact on close. That, combined with a regularly scheduled back-end compact, should:
a) keep the back-end size from becoming excessively bloated with dead space
b) keep the front-end size down and the table links refreshed and intact