Multi-Table Database Help (1 Viewer)

Lukasz337

New member
Local time
Today, 08:15
Joined
Jul 24, 2015
Messages
9
Hi Everyone,


So this is my first project with access, and I was wondering if I could get some advice/direction with the project. I was asked to create a new database for an office of about 100 people (all information has already been collected). The database consists of the following information, which I have broken up into separate tables.


-Wires_TBL: Every office/cubicle has a Ethernet wall port which is specific to that location. I am using this as the piece of information that ties everything else together
-Users_TBL: Every user in the office and their personal information
-Desktops_TBL: Every desktop and their respective serial number, hardware info, etc
-Monitors_TBL: Every monitor and its information
-Phones_TBL: Every phone and its information
-Printers_TBL: Every Printer and its information


Here is how they tie together:
-The printers are for the most part independent from the other information, some users have their own printers, but this information is on the bottom of my priority list
-Every Wire/Ethernet Port has a User, Desktop, Monitor, and Phone attributed to it. Simple, but here is where I run into my problem, each "workspace" has 2-3 monitors and 1-2 phones. I am having a lot of trouble correctly linking the information to accomplish the following:


-Display all attributed information for a given Wire, including all the monitors and phones for that workspace. (Mostly solved, I figured out how to do this with a many-to-many and a subform)
-Enter new data for each form separately so the individual tables can be linked later. For example, I add a new User, 3 Desktops, and 5 Monitors (I know how to accomplish this part with a entry form based off of each table). I would then like to assign the new User to an ex-employees Wire Number, leave the New User with a the old users Phone, assign him one of the new Desktops and 2 new Monitors. Is this doable? and if so can someone point me in the right direction? I am very lost as to what to do.





Here is how I set up my relationships.
-Users (userID_PK) :1toMany: Wires (userID_FK)
-Desktop (PCserial_PK) :1toMany: Wires (PCserial_FK)
-Monitors (monitorID_PK) :ManytoMany: Wires (wire number_PK)
-Phones (MACadress_PK) :ManytoMany: Wires (wire number_PK)
 

plog

Banishment Pending
Local time
Today, 10:15
Joined
May 11, 2011
Messages
11,611
The printers are for the most part independent from the other information, some users have their own printers, but this information is on the bottom of my priority list

I hear this all the time and this will bite you. "90% of our data is like this, and for now we can forget about this 10% because...". No, you really can't. If you want to handle a special case, deal with it now. You can't easily add add a storm cellar 3 months after you've laid the foundation and working on dry walling the 2nd floor even though that storm cell is really just 30 square feet.


-Users (userID_PK) :1toMany: Wires (userID_FK)
-Desktop (PCserial_PK) :1toMany: Wires (PCserial_FK)
-Monitors (monitorID_PK) :ManytoMany: Wires (wire number_PK)
-Phones (MACadress_PK) :ManytoMany: Wires (wire number_PK)

I really question your relationships and assumptions. Not saying your wrong, just don't know if its best.

1. Every piece of equipment goes to a user (via the wires table). What about community/shared equipment? What about unassigned/spare/backup equipment?

2. Some of your equipment doesn't plug directly into ethernet. Would monitors be better assigned to the computer table?

3. You have any cubicles with multiple wires? You named the table Wires, but you said it described a location. Is it truly one wire per location? Or can some have multiple locations?

4. You might just need one Equipment table to house all your equipment (phone, monitor, printer, computer) instead of individual tables for each type. What data fields are you storing with each type of equipment?


My main point is that you really need to think about your data in a hierarchy and if that hierarchy really represents what is going on. Are computers really assigned to a wire or to a person or to a location?
 

Minty

AWF VIP
Local time
Today, 15:15
Joined
Jul 26, 2013
Messages
10,354
You shouldn't have a separate table for each type of equipment. If you add a new piece of equipment (lets say Holographic Lenses) you would have to add another table and then redesign all your queries / forms etc. to cope.

You should try and have an equipment type table, a user table and the a User Equipment Table that joins them.

tblEquipmentType
EquipTypeID(PK)
EquipTypeDescription
...other data
tblUsers
UserId(PK)
... other user data
tblUSerEquipment
EquipmentID(PK)
EquipTypeID(FK)
UserID(FK)
SerialNumber

I would possibly have a Location table rather than a wires table - not everything plugs into a wire...

You can then add a new equipment type and it would be included in your existing queries.

This will simplify your problem.

[Edit] - PLog beat me to it... (Again)
 

Lukasz337

New member
Local time
Today, 08:15
Joined
Jul 24, 2015
Messages
9
I hear this all the time and this will bite you. "90% of our data is like this, and for now we can forget about this 10% because...". No, you really can't. If you want to handle a special case, deal with it now. You can't easily add add a storm cellar 3 months after you've laid the foundation and working on dry walling the 2nd floor even though that storm cell is really just 30 square feet.
You're right. I figured it just adds an additional tier of complexity which is out of the scope of my question. Maybe 20 users have personal printers, however there are 5 main printers everyone has access to.




1. Every piece of equipment goes to a user (via the wires table). What about community/shared equipment? What about unassigned/spare/backup equipment?

There is no shared equipment (other than a few printers). As of now, there is no unassigned/backup equipment in the database. The way the old database was set up was that you searched for a User, then overwrote the connected information (ie Desktop information). I don't think this is a valid way to keep records.

2. Some of your equipment doesn't plug directly into ethernet. Would monitors be better assigned to the computer table?

3. You have any cubicles with multiple wires? You named the table Wires, but you said it described a location. Is it truly one wire per location? Or can some have multiple locations?
The reason why I chose to use the Wire Numbers is because they are the only thing in the office that is permanent. The cubicles and offices are unnumbered, so the wire number serves as the identification of a location.




4. You might just need one Equipment table to house all your equipment (phone, monitor, printer, computer) instead of individual tables for each type. What data fields are you storing with each type of equipment?
Each piece of equipment has around 15 pieces of information such as serial number, product number, model name, purchase date, purchase price, etc.



The reason why I don't see one table working is that to keep the columns from repeating, I would get rows that repeat.


-Situation 1:
Wire 1, User 1, Desktop 1, Monitor 1a, Monitor 2a, Monitor 3a
Wire 2, User 2, Desktop 2, Monitor 1b, Monitor 2b, Monitor 3b
How would the database know that the Monitor 1,2,3 columns are the same thing type of equipment? When assigning a new monitor, how would the database know to pull a list from all 3 columns?


-Situation 2:
Wire 1, User 1, Desktop 1, Monitor 1a
Wire 1, User 1, Desktop 1, Monitor 2a
Wire 1, User 1, Desktop 1, Monitor 3a
I now have three times the records

Are computers really assigned to a wire or to a person or to a location?
The way I see it, Wire = Location. Users, and each individual piece of equipment can be moved, retired, or changed daily.
 

Lukasz337

New member
Local time
Today, 08:15
Joined
Jul 24, 2015
Messages
9
You should try and have an equipment type table, a user table and the a User Equipment Table that joins them.

tblEquipmentType
EquipTypeID(PK)
EquipTypeDescription
...other data
tblUsers
UserId(PK)
... other user data
tblUSerEquipment
EquipmentID(PK)
EquipTypeID(FK)
UserID(FK)
SerialNumber



So if I understand this correctly, all equipment, regardless of its whether its a monitor or desktop or whatever, would be in one column? I'm not sure I understand why the Serial Number would be under tblUserEquipment.
 

Minty

AWF VIP
Local time
Today, 15:15
Joined
Jul 26, 2013
Messages
10,354
The UserEquipment table is where you would store the actual equipment details and assign them to a user (or not) Maybe even call it the Equipment table. (Not the equipment type obviously)
 

JLCantara

Registered User.
Local time
Today, 08:15
Joined
Jul 22, 2012
Messages
335
@Lukas: I think your initial schema is right. I find some of your relationships strange. May I suggest:

Wires --> Users
Wires --> Desktops
Wires --> Monitors (if desktop connection can be omitted)
Wires --> Printers
Wires --> Phones

For unconnected equipment, create a dummy WireID.
 

Lukasz337

New member
Local time
Today, 08:15
Joined
Jul 24, 2015
Messages
9
@Lukas: I think your initial schema is right. I find some of your relationships strange. May I suggest:

Wires --> Users
Wires --> Desktops
Wires --> Monitors (if desktop connection can be omitted)
Wires --> Printers
Wires --> Phones

For unconnected equipment, create a dummy WireID.



For the Wires --> Monitor and Wire --> Phone relationships I guess my question is how do I do this without creating double entries, Wire 1:Monitor 1 and Wire 1:Monitor 2?
Setting it up I this manner produces 200 entries for me as opposed to 100, since each monitor creates a new row in the Wire table which I'm using as the "Master" table (not sure of the correct phrasing for this)
 

Lukasz337

New member
Local time
Today, 08:15
Joined
Jul 24, 2015
Messages
9
Could I simplify everything down to one table? Each row corresponds to one Wire and all associated equipment and users. Run Queries from this table to break down the information (Queries for users, desktops, monitors, etc). Then set a VB script that performs along the lines of:
On lost focus
find duplicate entries and delete old

This way if a desktop is moved between locations, the old location(wire) will have an empty field?
 

JLCantara

Registered User.
Local time
Today, 08:15
Joined
Jul 22, 2012
Messages
335
You not familiar with the concept of relationships.
Since you have 2 monitors you need 2 records but not in the Wires table but in the Monitors table.

At this point this can be a solution:

Wires
Wire: PK

Monitors
Monitor: PK
Wire: FK

So 1 entry in Wires point to 2 entries in Monitors... with no duplications.
 

Users who are viewing this thread

Top Bottom