Relationship Question

[Stranger]

I'm setting up an inventory for IT hardware. I've been debating on the relationships.

Currently I have the following:

tblhardware
tblemployees
tblsites
tblemployeehardware

the relationships are:

tblhardware.hardwareID 1-> many tblemployeehardware.hardwareid
tblemployees.employeeid 1 -> many tblemployeehardware.employeeid
tblsites.siteid 1 -> many tblemployeehardware.siteid

Now,

1 employee can have multiple hardware Id's (PC, laptop, etc) at one time..
and multiple employees can be assigned to 1 PC.

1 employee can be assigned to multiple sites
1 site has multiple employees.

I hope this sounds clear.

does it look like I have this correct?

 

[Stranger]

In the sites table I have siteid, site, and location.

Since there are only 2 sites and eventually a 3rd. Do I need to somehow make it so I only have the site listed once with all of the locations under? Or just leave as is and have the same site name listed multiple times? If I have it where each site is only listed once, how would I do this?
I'm setting up an inventory for IT hardware. I've been debating on the relationships.

Currently I have the following:

tblhardware
tblemployees
tblsites
tblemployeehardware

the relationships are:

tblhardware.hardwareID 1-> many tblemployeehardware.hardwareid
tblemployees.employeeid 1 -> many tblemployeehardware.employeeid
tblsites.siteid 1 -> many tblemployeehardware.siteid

Now,

1 employee can have multiple hardware Id's (PC, laptop, etc) at one time..
and multiple employees can be assigned to 1 PC.

1 employee can be assigned to multiple sites
1 site has multiple employees.

I hope this sounds clear.

does it look like I have this correct?

 

[Rebecca Riordan]

You should split site and location into separate tables, and you'll also
need another junction table to resolve the many-to-many between site and
employee. Just like the EmployeeHardware table, this should contain the ids
of both primary tables.

HTH

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

In the sites table I have siteid, site, and location.

Since there are only 2 sites and eventually a 3rd. Do I need to somehow
make it so I only have the site listed once with all of the locations under?
Or just leave as is and have the same site name listed multiple times? If I
have it where each site is only listed once, how would I do this?
I'm setting up an inventory for IT hardware. I've been debating on the
relationships.

Currently I have the following:

tblhardware
tblemployees
tblsites
tblemployeehardware

the relationships are:

tblhardware.hardwareID 1-> many tblemployeehardware.hardwareid
tblemployees.employeeid 1 -> many tblemployeehardware.employeeid
tblsites.siteid 1 -> many tblemployeehardware.siteid

Now,

1 employee can have multiple hardware Id's (PC, laptop, etc) at one time..
and multiple employees can be assigned to 1 PC.

1 employee can be assigned to multiple sites
1 site has multiple employees.

I hope this sounds clear.

does it look like I have this correct?

 

[Stranger]

Here is what I did:

I split the tblsites and created

tblsites.site
tblsites.siteid - Primary key

tbllocations.location
tbllocations.locationid - Primary Key

Created tblsitelocation (SiteID, LocationID)

then made the following relationships:

tblsites.siteid 1-> many tblsitelocation.siteid
tbllocations.locationid 1-> many tbllocations.locationid

I believe I need to join the tblsitelocation table back to the
tblemployeehardware?

I tried creating a tblsitelocation.sitelocationid and make the relationship
back to tblemployeehardware.sitelocationid but it will not let me do this.

???

 

[Stranger]

I think I got it. Is this correct?

Here is what I did:

I split the tblsites and created

tblsites.site
tblsites.siteid - Primary key

tbllocations.location
tbllocations.locationid - Primary Key

Created tblsitelocation (SiteID, LocationID, EmployeeID)

then made the following relationships:

tblsites.siteid 1-> many tblsitelocation.siteid
tbllocations.locationid 1-> many tbllocations.locationid

Then made a 1-> many from tblemployees.employeeid to
tblsitelocation.employeeid

Does this look right now?

 

[Rebecca Riordan]

Yes, that's exactly what I meant. Does it seem to be working for you?

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

 

[Stranger]

Hi!!

I made one more change. I think this looks like it may work better. What
do you think?

tblhardware.hardwareid(primary key) 1 -> many tblemployeehardware.hardwareid
tblemplyeehardware.employeeid many -> 1 tblemployees.employeeid (primary
key)
tblemployees.employeeid (primary key) 1 -> many tblsitelocation.employeeid
tblsitelocation.siteid many -> 1 tblsites.siteid
tblsites.siteid (primary key) 1 -> many tbllocations.siteid

Does this look better?

I'm thinking that this will allow 1 hardware to have many employees and one
employee to be assigned to mulitple hardware.
1 site can have many employees
1 site can have many locations.

BUT, an employee can be assigned to mulitple sites. Is this working? I get
so confused with these relationships.


--

 

[Stranger]

Hi!!

I think i go it. I went back to the design you said was ok. I've populated
the tables from the flat table since I assigned ids to everything so I can
link them together in the intermediate tables. Everything is looking good.

thanks for all your help.

 

[Stranger]

When I click on the plus by an employee it asked me which subdatasheet to
use. Which one should I pick?

 

[Rebecca Riordan]

Depends on what you want to see...I should think hardware, but I'm only
guessing.

--
Rebecca Riordan, MVP

Seeing Data: Designing User Interfaces
Designing Relational Database Systems, 2nd Edition
www.awprofessional.com

Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step
www.microsoft.com/mspress

 

[Stranger]

Could I use a query that would show, hardware, site and location? If so,
would I include the ID's in the query? would this help when I start created
the forms?

 

[Stranger]

It would not let me pick the hardware table. I picked the .... I forgot
but in the master and child link fields it auto filled in the employee ID so
I went with that.

So, when I click on the plus in the table hardware, I see the employee ID.

When I click the plus in the employee table, I see the site and location ID.
I guess this is ok.