Question Help on a Design Relationship Problem ??

[Greycom4]

Database to manage resource at a number of buildings.
Resource is very similar in each building, for example

Computer Model (A)
Computer Model (B)
Phone Model 1
Phone Model 2
Desk Printer


The challenge is I would like to track up to 200 desks in 10 separate buildings. These buildings will be used by multiple teams (up to 30 per site) who have the default resource configured differently on each desk.

I can setup a many 2 many relationship between the Buildings and the Teams to get a unique entry for every desk and team at each building. My problem now is how can I relate each resource (possibly multiple) to each desk. I could create a Resource Table and link that to my data but the only way I can see this working is to create a column for every resource to be able to flag if the
Resource needs to be present. Is there a smarter way to do this.

Thanks

 

[Greycom4]

Access 2007 - Multiple Many 2 Many Relationships

Database to manage resource at a number of buildings.
Resource is very similar in each building, for example

Computer Model (A)
Computer Model (B)
Phone Model 1
Phone Model 2
Desk Printer


The challenge is I would like to track up to 200 desks in 10 separate buildings. These buildings will be used by multiple teams (up to 30 per site) who have the default resource configured differently on each desk.

I can setup a many 2 many relationship between the Buildings and the Teams to get a unique entry for every desk and team at each building. My problem now is how can I relate each resource (possibly multiple) to each desk. I could create a Resource Table and link that to my data but the only way I can see this working is to create a column for every resource to be able to flag if the
Resource needs to be present. Is there a smarter way to do this.

Thanks

 

[nigelh77]

Basically many to many relationships are not a good idea. Try to keep all relationships as one to many creating intermediary tables if necessary. Here each building can have many desks but each desk should only have one building. If it moves from one building to another it should be disconnected from building A and connected to building B. Similarly each desk can presumably contain many resources but each resource will only have one desk.

Without knowing exactly what your requirement is I would suspect you need the following tables
Buildings
Desks - Containing a Building Id to create a link to the building
and a team id to link to the team
Resource - containing a Desk Id to link to a desk
Team - containing a building Id to link to the building

What you need is 'normalisation' of your database - google it

 

[catalin.petrut]

Re: Access 2007 - Multiple Many 2 Many Relationships

The way i see it: each resource is located on a desk, on a specific building. Each team has acces to a specific resource.
Tables needed:
Resouce
Desk (one 2 many resource)
Building (one 2 many desks)
Person (many 2 many resource)
Team (one 2 many person; many 2 many person - depends on your data)
For each many 2 many relationship you will need a junction table.

 

[Greycom4]

Thanks Nigelh77 for your reply.

I have will take on board your comments and investigate the Many 2 Many table position. I have already spend some time around the 1NF, 2NF and 3NF and that was the original reason for posting as I thought the design was not properly normalised. Seems like I have more to do here.

cheers

 

[Greycom4]

Re: Access 2007 - Multiple Many 2 Many Relationships

Thanks Catalin.Petrut for your response.

I am going to use your suggested table setup to work through some examples. I have read elsewhere to avoid many 2 many table relationships, but this seems standard design in most relational databases of this type.

Cheers

 

[catalin.petrut]

Re: Access 2007 - Multiple Many 2 Many Relationships

I cant see how someone can sugest to avoid many 2 many relationship. This kind of relationship is used when the reality demands it. Keep us informed with your progress.
PS: use the "thanks" button when you consider.

 

[Pat Hartman]

Basically many to many relationships are not a good idea.

Relationships are what they are. There is no reason to attempt to implement a relationship in some other "preferred" model. The relational model does not directly implement m-m relationships. They are implemented as a pair of 1-m relationships.

tblLocation
LocationID (autonumber PK)
LocationName
etc.
tblDesk
DeskID (autonumber PK)
LocationID (FK to tblLocation)
etc.
tblResource
ResourceID (autonumber PK)
ResourceName
SerialNum
PurchaseDT
DeskID (foreign key to tblDesk)
etc.
tblTeam
TeamID (autonumber PK)
TeamName
etc.
tblTeamLocation
LocationID (FK to tblLocation)
TeamID (FK to tblTeam)
tblSoftware
SoftwareID (autonumber PK)
SoftwareName
SoftwareVersion
etc.
tblResourceSoftware
ResourceID (FK to tblResource)
SoftwareID (FK to tblSoftware)

I don't have any where near enough information to create your actual schema but this should get you started.

 

[Pat Hartman]

PS, I merged the two threads you had on this topic.
Please don't create multiple threads on the same topic but if you do, ALWAYS put a link to the earlier post so the rest of us don't waste our time rehashing the issues.

 

[Greycom4]

Thanks Pat understood. I was having real performance issues with the site over Thursday and Friday and did mean to delete one topic over the weekend.

Cheers

 

[Pat Hartman]

Unless something is a complete duplicate, I wouldn't recommend deleting it. Moderators will merge threads if they notice the duplicates. And people searching the forum need to be able to find threads that cover the topic they are search on.