3 Part Compound Key

[Bee]

Hi,

I have searched on this forum about a solution to this and found out that 3 part compound keys are unwise although they are legal. Now, I am not sure how to design this database!

Basically, I need to store information about site (a big part of land), phase (a portion of site) and Plot (a portion of phase). Each record should represent a unique value of this three part combination. For example:

Record 1:
Site = Site1
Phase = 1
plot= 1

Record 2:
Site = Site1
Phase = 1
plot= 2

Record 3:
Site = Site1
Phase = 2
plot= 1

I have desinged three different tables since I need quite a number of relevant fields for each table. One table for site, another for phase and another one for plot.

I am thinking to have:

Site table -
siteName -> PK

Phase table -
siteName and PhaseNo -> compound PK
siteName -> FK

Plot table -
siteName, PhaseNo, PlotNo -> compound PK
siteName and PhaseNo -> FK

My concern is how to do their indexes and not allow duplicates of a whole compound key in access! Can access do it and how?

Any suggestions will be very much appreciated.
B

 

[neileg]

A compound key and a compound index are two different constructs. You can apply a compound index on fields that do not create a key. My preference is always to have a meaningless key (an autonumber) and to apply indexes to other fields if required. This allows the benefits of eliminating duplicates, but keeping the primary key nice and simple.

 

[Bee]

A compound key and a compound index are two different constructs. You can apply a compound index on fields that do not create a key. My preference is always to have a meaningless key (an autonumber) and to apply indexes to other fields if required. This allows the benefits of eliminating duplicates, but keeping the primary key nice and simple.

I don't quite understand your post neileg. Do you mean I can index two field that are not PK with an compound index? Not sure how to do that...

 

[neileg]

Open the table in design view a click the Index Button on the toolbar. In the next empty row, in the Index Name column, enter a name of your choice. In the Field Name column, add the names of the fields you want to index, one to each row. Its basically the same as setting the compound primary key, only you don't call it PrimaryKey.

 

[Bee]

I managed to find and create indexes; however, I still haven't managed to get the functionality I want. Do I need to set both of them to Unique and Ignore Nulls?

I want to be able to enter records where half of the (compound index) can be duplicated, but both can't. For example:

Record 1:
Indexed Field1: 1
Indexed Field2: 1

Record 2:
Indexed Field1: 1
Indexed Field2: 2

The above should be allowed where the records below should not.

Record 1:
Indexed Field1: 1
Indexed Field2: 1

Record 2:
Indexed Field1: 1
Indexed Field2: 1

Thanks,
B

 

[Steve R.]

I don't think you have your question phrased correctly. The structure of your database does not appear to be correct based on my understanding of your question. If I correctly understand what you want, you may only need one table.

First, your site number and plot number should be merged into one unique number. For example, for site #1 and plot #1 you could use 001001. This is very similar to how property records are kept. The phase number would then be another field in that table. You should also have an autonumber field, but do NOT NOT use it to for identifying your parcel number. The autonumber is used to link your data to other tables.

If you are wondering: how can I get a dump of all records for site#1? - there are several ways to accomplish this. The easiest way may be to use the LIKE operator, you can use the LEFT function, or you could use a BOOLEAN value.

To get more complex results, such as all the lots in phase 3 (whatever that may be) you would need to develop an SQL query.

SELECT * FROM mytable WHERE phaseid=3

 

[Bee]

I don't think you understood my question ortaias. How would I know what plot in what phase if I just reference site and plot directly? Can you please elaborate?

Thanks,
B

 

[Steve R.]

Each record in MyTable would have the following format:
autonumber, UniqueSitePlotNumber, Phaseid

So you can access the phase of development that a certain lot is in by using the following:

SELECT phaseid FROM MyTable WHERE UniqueSitePlotNumber = SomeValue

 

[neileg]

I managed to find and create indexes; however, I still haven't managed to get the functionality I want. Do I need to set both of them to Unique and Ignore Nulls?

You shouldn't have two indexes, you should have one index with two fields. In the IndexName column the first row has the index name and the second is blank. Put your cursor in the index name cell and set Unique to Yes. You shouldn't have any nulls so this setting doesn't matter.

I want to be able to enter records where half of the (compound index) can be duplicated, but both can't.

Yes, that's what you should get.

 

[Bee]

Each record in MyTable would have the following format:
autonumber, UniqueSitePlotNumber, Phaseid

So you can access the phase of development that a certain lot is in by using the following:

SELECT phaseid FROM MyTable WHERE UniqueSitePlotNumber = SomeValue

My concern with that approach is if I use a merged UniqueSitePlotNumber, it won't help solve this issue. Plot numbers can repeate within a certain site, but they can't repeat within a certain phase. So, by using UniqueSitePlotNumber, there will be no way to stop repeating enteries for plots within phases. For example: we can have three plots numbered 36 in site1, but each of this plot has to be in a different phase.

That's why I thought by having a compound key or compound index as Neil suggested would solve the problem because it won't allow the same combination entered twice. In other words, it will allow duplicate plots as long as they are associated with different phases.

 

[Steve R.]

SELECT * FROM MyTable WHERE UniqueSitePlotNumber = SomeValue AND phaseid = phasevalue

OR

SELECT * FROM MyTable WHERE SiteNumber = SomeValue AND PlotNumber = AnotherValue AND phaseid = phasevalue

The above will return a unique value.

 

[Bee]

Your queries are great and they would work when pulling data from the database. I actually want to restrict entering duplicate plots into any one phase in the database. We are in two different directions I think