Modifying a flat database to create a relational

[clchildress]

I currently have a flat database which has been functioning fairly well for some time, but which we are beginning to outgrow. Keeping track of maintenance costs, user and computer allocations, and other matters is becoming a real chore. What I want to do is make this relational so that I don't have to enter redundant information, and so that eventually (once I've learned how) calculate total cost of ownership for fixed assets.

I'm running into a problem where I seem to have multiple many-to-many relationships, and so I'm looking for some advice on how to modify my database to work as a relational one.

The first many-to-many is that I have locations with multiple employees and multiple employees that work at different locations. I need to represent all of the locations a particular employee works at, but I need to see which location is their *home* location.

The second many-to-many is that I have some employees using multiple computers, often with a preferred or regular computer. Many computers are often used by multiple employees. I need to represent which employees use each computer (in a list), and when viewing the employee I need to see which computers that employee uses.

The next problem is maintenance. I need to be able to show all maintenance records for a particular location so that I can (eventually) calculate total cost. I also need to be able to show all maintenance done to a particular fixed asset so that I can compare relative cost/worth of a particular fixed asset (say, the purchase price of a given printer, vs. what I've already doled out in maintenance costs).

I know this is a lot to ask, and I can't ask it all of anyone, but if a few people could give me some pointers it would be *much* appreciated!

I have attached an empty clone of my database so you can see what tables/fields/values I'm working with. Hopefully this will help.

Assets__Users_and_Maintenance_Clone.zip

[Ender]

Welcome clchildress,

I made an ERD for a similar setup a while back which should be helpful (see attached). You can ignore the part about software license assignments if you wish.

To also track the primary computer for a User, I'd suggest either adding a PrimaryEquipmentID field in User, or adding a flag in the Equipment_Assignment join table that indicates that that piece of equipment is primarily in that user's possession.

Note that the Equipment table in this model is a more general term that should work for any type of equipment that gets assigned to a user. That means the Computers, Printers, and Faxes (plus external hard drives, monitors, etc.), would all be in that same table. While this structure might have some fields that aren't used for all types of Equipment, it will be better for reporting, and make the interface a little easier to design.

One thing I'm not sure I saw in your tables is a primary key for each table. I'd suggest adding a RecordID field (auto-enter serial number) and use this for relationships. Add corresponding foreign keys to other tables as needed. Even if your equipment generally has a manufacturer's serial#, I'd still recommend using this separate RecordID for primary relationships.

[clchildress]

Thanks; I think this can get me on the right track. I'm just very new to relational databases and it takes a certain way of thinking. I'll try this out and see how it works.