By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,862 Members | 868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,862 IT Pros & Developers. It's quick & easy.

Relation / Table Structure Question. Location Heirarchy. Please help.

P: n/a
I am working on an access 2000 DB for some tree-growers that will be
storing items in a heirarchy of locations. The items will obviously
be stored at the lowest level in the heirarchy (in a row) but the rows
are grouped into sections, and sections are grouped into houses.

Here is a picture. I'm sure you understand already.

HouseA -> Section A -> Row1
-> Row2 -> item
-> Row3
Section B -> Row1
-> Row2
HouseB -> Section A -> Row1

etc.

I am having problems creating the tables and relationships. I
currently have the House Table with Housename as Pkey. Section Table
has Sectionname AND Housename as PKey. Row Table has Rowname AND
Sectioname as Pkey. They are all related appropriately back up the
heirarchy so that the deletes/updates work appropriately.

This works o.k. except for every house/section/row must have a unique
value for the names in the pkey. That is a problem that I can live
with.

The problem that I CAN'T live with is that queries are extrememly
difficult. "What rows are in HouseA" is even giving me problems at
this point.

If you could please give me any suggestions on how I could create the
tables and relationships for this heirarchy properly it would be
great. I betcha there is a standard way to solve this heirarchy
problem.

Thanks a bunch,

Dave.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Dave,

You need a rather standard query using Totals listing Houses, Sections and
Rows, with a standard Group By everything, and the usual links.

Then use that query and have House="House A"

Nov 12 '05 #2

P: n/a
Dave, I think you'll be well-served by using an Autonumber in, at least, the
section and row tables as a "surrogate key" to simplify relationships and
joins. BTW, the Row table will need to include foreign keys to both the
house and section tables if you choose to continue with using names as keys
(aka "natural keys").

Larry Linson
Microsoft Access MVP
"Dave" <go****@xenogear.net> wrote in message
news:4d*************************@posting.google.co m...
I am working on an access 2000 DB for some tree-growers that will be
storing items in a heirarchy of locations. The items will obviously
be stored at the lowest level in the heirarchy (in a row) but the rows
are grouped into sections, and sections are grouped into houses.

Here is a picture. I'm sure you understand already.

HouseA -> Section A -> Row1
-> Row2 -> item
-> Row3
Section B -> Row1
-> Row2
HouseB -> Section A -> Row1

etc.

I am having problems creating the tables and relationships. I
currently have the House Table with Housename as Pkey. Section Table
has Sectionname AND Housename as PKey. Row Table has Rowname AND
Sectioname as Pkey. They are all related appropriately back up the
heirarchy so that the deletes/updates work appropriately.

This works o.k. except for every house/section/row must have a unique
value for the names in the pkey. That is a problem that I can live
with.

The problem that I CAN'T live with is that queries are extrememly
difficult. "What rows are in HouseA" is even giving me problems at
this point.

If you could please give me any suggestions on how I could create the
tables and relationships for this heirarchy properly it would be
great. I betcha there is a standard way to solve this heirarchy
problem.

Thanks a bunch,

Dave.

Nov 12 '05 #3

P: n/a
Dave wrote:

This works o.k. except for every house/section/row must have a unique
value for the names in the pkey. That is a problem that I can live
with.


No, that's going to cause you problems. Surely a house name can
change. Even if you don't think so, your clients *will*, I guarantee it
eventually want to change names of houses, rows, sections, and items.

You are much, much better off using a system generated number such as an
autonumber field as your PK for the house and system identifiers for the
other tables. You can index the house name with no duplicates allowed
(and indexing is a good thing as you'll be searching on the house name
frequently).

I'm not sure I understand the exact relationship you have indicated,
either, but I wouldn't have multiple fields as PK. I'd make the PK for
each entity the identifier. In other words, do the same thing as I've
just described for house. Then, realising the item is the end of the
hierarchy and that an item can only can be assigned once to a row, a row
assigned once to a section, a section once to a house, then you map out
your realtionships that way.

It's generally not a good idea to make a label that your users will see
and use as the means for effecting joins or creating PKs. Leave that to
system numbers they can't see and they'll be happy. There are some
instances when you can use an identifier as a record identifier -
account numbers come to mind as long as your organization does not
change such account numbers.

Anyway, hope this helps.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto
Nov 12 '05 #4

P: n/a
Also, just reread your description. The foreign keys need top go from
the lowest level up. Thus:

tblHouse
--------
HouseName
HousePK

tblSection
----------
SectionName
SectionPK
SectionHouseFK (connected to the HousePK)

tblRow
------
RowName
RowPK
RowSectionFK (connected to the SectionPK)

tblItem
-------
ItemName
ItemPK
ItemRowFK (connected to the RowPK)

It is *not* necessary and in fact will make things very complicated
especially when you move things around if you have combined field PKs
you described.

Just connect one item in the hierarchy to the next one up.
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Want some?" - Ditto
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.