Jump to content

Modifying a flat database to create a relational


clchildress

This topic is 6134 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

software_equipment.GIF

Link to comment
Share on other sites

This topic is 6134 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.