Table Relationship Question

Table Relationship Question

I have an Equipment table and three location tables:

tblEquipment
EquipmentID  int  PK
SiteID           int  FK
LocationID     int   FK
SubLocation   int  FK
...other fields

tblSite
SiteID int  PK   
Site    char  (50)

tblLocation
LocationID  int  PK
SiteID        int  FK
Location     char(50)

tblSubLocation
SubLocationID   int   PK
LocationID        int    FK
Sublocation      char  (50)


Every tblEquipment record may or maynot have a site, location or sublocation 
associated with it. 

Is this the proper table relationship structure?
Thanks for any help.

date: Thu, 7 Aug 2008 13:58:01 -0700 author: AkAlan

Re: Table Relationship Question

The way you designed it is definitely an option.  Another option would
be to have the location/site/sublocation be one table that references
itself...

tblEquipment
EquipmentID  int  PK
LocationID           int  FK
...other fields

tblLocation
LocationID  int  PK
Parent_LocationID int  FK
Location     char(50)

...where the location can have a parent location and the equipment has
only one location id.

Both your design and this proposed design have pros and cons.  The one
you proposed it probably the fastest but it might be more difficult to
develop against or change.

-Eric Isaacs

date: Thu, 7 Aug 2008 14:39:46 -0700 (PDT) author: Eric Isaacs

Re: Table Relationship Question

What might be a better option is to not have SiteId in the equipment table; 
rather have the Site table have a FK for the eqipment table. That does away 
with nulls in the siteid column.
Roy

"Eric Isaacs" wrote:

> The way you designed it is definitely an option.  Another option would
> be to have the location/site/sublocation be one table that references
> itself...
> 
> tblEquipment
> EquipmentID  int  PK
> LocationID           int  FK
> ....other fields
> 
> tblLocation
> LocationID  int  PK
> Parent_LocationID int  FK
> Location     char(50)
> 
> ....where the location can have a parent location and the equipment has
> only one location id.
> 
> Both your design and this proposed design have pros and cons.  The one
> you proposed it probably the fastest but it might be more difficult to
> develop against or change.
> 
> -Eric Isaacs
>

date: Thu, 7 Aug 2008 16:07:02 -0700 author: RAF

Re: Table Relationship Question

On Aug 7, 4:07 pm, RAF  wrote:
> What might be a better option is to not have SiteId in the equipment table;
> rather have the Site table have a FK for the equipment table. That does away
> with nulls in the siteid column.
> Roy

Then a site could have only one piece of equipment.  Equipment is
likely to have only one site, location, and sub-location.  They way
AkAlan had it designed was correct.  SiteID probably shouldn't ever be
null in his design, but the challenge with that design is keeping the
data valid.  In other words, if there is a location id in the
equipment table, it should only be a location that's within the
SiteID .

If you have a shared table as I suggested, you can have only one
location, but that location can be a site, location, or sub-location.
That removes the issue with the sites, locations, and sub-locations
getting out of sync, and you can still determine sites, locations and
sub-locations, or if a piece of equipment is in a site, with that
design.  But the queries run a bit slower, as the optimizer has more
trouble optimizing the query to hit multiple levels of the same
table.  But there are also a few tricks to work around these
optimization issues.

-Eric Isaacs

date: Fri, 8 Aug 2008 09:23:19 -0700 (PDT) author: Eric Isaacs

RE: Table Relationship Question

Thanks for both replies. Eric you have a handle on what I'm trying to 
accomplish. There are 15 fixed sites that the equipment can be (usually 
permanently) and each site has it's own unique locations, buildings and such 
with with sublocations, rooms, cabinets, etc. I plan on the site managers 
populating the three tables and then use drop downs to assign each equipment 
item their specific location. Thanks again for taking the time to post a 
reply.

"AkAlan" wrote:

> I have an Equipment table and three location tables:
> 
> tblEquipment
> EquipmentID  int  PK
> SiteID           int  FK
> LocationID     int   FK
> SubLocation   int  FK
> ...other fields
> 
> tblSite
> SiteID int  PK   
> Site    char  (50)
> 
> tblLocation
> LocationID  int  PK
> SiteID        int  FK
> Location     char(50)
> 
> tblSubLocation
> SubLocationID   int   PK
> LocationID        int    FK
> Sublocation      char  (50)
> 
> 
> Every tblEquipment record may or maynot have a site, location or sublocation 
> associated with it. 
> 
> Is this the proper table relationship structure?
> Thanks for any help.
> 
>

date: Fri, 8 Aug 2008 12:57:01 -0700 author: AkAlan

Re: Table Relationship Question

According to the original post:

"Every tblEquipment record may or maynot have a site, location or sublocation 
associated with it. "

Which I take to mean that equipment may not have a site. If, indeed, that is 
the case then the siteid would have to be a null.

"Eric Isaacs" wrote:

> On Aug 7, 4:07 pm, RAF  wrote:
> > What might be a better option is to not have SiteId in the equipment table;
> > rather have the Site table have a FK for the equipment table. That does away
> > with nulls in the siteid column.
> > Roy
> 
> Then a site could have only one piece of equipment.  Equipment is
> likely to have only one site, location, and sub-location.  They way
> AkAlan had it designed was correct.  SiteID probably shouldn't ever be
> null in his design, but the challenge with that design is keeping the
> data valid.  In other words, if there is a location id in the
> equipment table, it should only be a location that's within the
> SiteID .
> 
> If you have a shared table as I suggested, you can have only one
> location, but that location can be a site, location, or sub-location.
> That removes the issue with the sites, locations, and sub-locations
> getting out of sync, and you can still determine sites, locations and
> sub-locations, or if a piece of equipment is in a site, with that
> design.  But the queries run a bit slower, as the optimizer has more
> trouble optimizing the query to hit multiple levels of the same
> table.  But there are also a few tricks to work around these
> optimization issues.
> 
> -Eric Isaacs
>

date: Fri, 8 Aug 2008 13:22:01 -0700 author: RAF

Re: Table Relationship Question

On Aug 8, 1:22 pm, RAF  wrote:
> According to the original post:
>
> "Every tblEquipment record may or maynot have a site, location or sublocation
> associated with it. "
>
> Which I take to mean that equipment may not have a site. If, indeed, that is
> the case then the siteid would have to be a null.

No disagreement from me on what you said above.  If site id is null in
the equipment table (or locationid is null in the equipment table in
my design) then the location of the equipment is unknown by the
database.

But your first suggestion was to put a foriegn key to the equipment
table in the site table (put EquipmentID in tblSite.)  That won't work
because then a site could have only one and only one equipment record
(but a piece of equipment could be in multiple sites, which probably
isn't what AkAlan needs.)

-Eric Isaacs

date: Fri, 8 Aug 2008 14:46:31 -0700 (PDT) author: Eric Isaacs