Re: one to one relationship help and database design question

Tech-Archive recommends: Speed Up your PC by fixing your registry


DawnTreader wrote:

note the left-to-right column order, MachineTypeID then MachineID,
[in the PRIMARY KEY declaration] is significant;

left to right and significant, why?

Briefly, physical ordering on disk ('clustered index').

In Access/Jet, the 'primary' index (the PRIMARY KEY constraint or in
its absence the chronologically first created UNIQUE constraint)
determines the physical ordering when the file is compacted (subsequent
rows are inserted in date/time order).

Think paper copy telephone directory. It can have only one physical
order (obviously). If it were ordered on telephone number, would this
be useful? No. It is ordered on last_name then first_names e.g. PRIMARY
KEY (last_name, first_names). The left-to-right order is significant
here because it is not the same as (first_names, last_name) first_names
then last_name, being an entirely different and less useful set
ordering. Physical ordering on last_name first will particularly favour
tasks such as, 'Grab me all the people who's last name begins with the
letter 'p'.

I've assumed having your 'main' table physically ordered on machine
type will be beneficial e.g. to GROUP BY queries. There is a counter
argument, and a good one, that physically ordering on a random integer
(e.g. autonumber) column will improve concurrency (although with Jet
4.0's record-level locking this argument is less strong). I assumed you
wouldn't be able to make a judgement on concurrency considerations
because, well, there doesn't seem to be anything relevant in the Access
documentation!

This is a bit of a controversial issue because historically the Access
documentation said to say that Jet does not 'support' (operative word)
clustered indexes
(http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/output/F1/D2/S5A274.asp)
whereas the more recent/detailed documentation confirms that a concept
of 'non-maintain clustered indexes' does indeed exist
(http://support.microsoft.com/default.aspx?scid=kb;en-us;137039 and
http://support.microsoft.com/default.aspx?scid=kb;en-us;209769). Excuse
me while I prepare to be flamed once more for daring to speak the
truth... <g>.

HTH,
Jamie.

--

.