Table Design thoughts

[Crowley16]

Hi everyone...

Currently I have a database to track items and locations. The basic way this is done is to have a location table and a locationID FKey in other tables.

however locations can either be other items or other locations which lives in different tables/queries. This means I can't put referential integrity on the joins to the locations table.

to complicate things further, there are different levels of items, and only certain levels of items can be locations, and the different levels have different tables. This means certain items can only be fitted to certain other items. However it would be better to have a more dynamic structure, i.e. any item could be fitted to any other item or location.

I was wondering if there was another neater way to implement something like this?

Thanks

--------------------
Procrastinate Now!

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Is there a reason that different levels have different tables? In other words do items in different levels have different fields (attributes)? If they do not then, this would be my design.

tblItems
itemID (PK)
intLevel
blnIsLocation (yes no if it can be a location)
strIsItem (default value = 'Item')
other item attributes

tblLocations
locationID (PK)
strIsLocation (default value = 'Location')
other field attributes

tblAssignments
itemIDfk (FK)
locationID (FK)
strLocationType (either 'Item' or 'Location')

refTblAllowedAssignments
intItemLevel
intAssignableLevels
(use this to produce queries to present the user with only the possible items that he could assign to another item or location in conjunction with the field blnIsLocation)

This would be my basic data structures, but obviously there would then have to be a lot of rules imposed on forms and queries.

I am doing something similar with the reference table. I have a table of equipment, and certain pieces of equipment need to be associated with other equipment types. So for example in my reference table i would have something like (using ID)
Radios Batteries
Radios Antennas
Rifles Optics
Rifles Ammunition

So if I add an item that is a radio, I am presented with all the batteries and antennas that I can associate it to.

 

[Crowley16]

yeah, the different levels have different properties and attributes...

infact for the lowest level of items, there's a seperate table just to hold the properties of the items but for the higher levels the properties are held within the same table.

but the link table between items and locations is sort of the system I use at the moment...

--------------------
Procrastinate Now!