Assets Database Design Help!!

[wakerider017]

I've recently inherited an excel spreadsheet with multiple tabs keeping track of multiple different models of phones / tablets / wifi cards issued to different users in different offices across the country. In a seperate spreadsheet we are keeping track of how many car/wall chargers we are sending out. And in a third spreadsheet we are recording when devices are returned to us.
There is a high turn over rate in our industry so I will constantly be swapping out devices. I need a better way to keep track of the Assets we are sending out and the assets being returned to me. I'm a little rusty with Access as I haven't used it in a few years, but I'm willing to brush up on it a little bit to creat a more refined solution.
Obviously I will need a table for:

Employee Info:
-ID*
-FirstName
-LastName
-Email
-OfficeLocation
-Notes

I will also need a way to keep track of the actual assets. Should the car and wall chargers have there own table? What would be the best way to keep inventory in order to record returned devices and keep track of items that have been shipped out?

Device Table:
-ID*
-Category (Phone,Tablet,WiFiCard,WallCharger,CarCharger)
-IMEI
-SIM (This can change)
-Make (LG,Samsung,Apple)
-Model (Galaxy,Spectrum,iPhone)
-PhoneNum (This can change)
-Carrier (Verizon, AT&T, T-Mobile)
-Price (Cost of deivce if lost or broken)
-Condition (New,Good,Fair,Poor)
-AquiredDate
-LineStatus (Active, Suspended)
-Office Location
-Notes

Order Table:
-ID*
-DateShipped
-TrackingNum
-FirstName
-LastName
-OfficeLocation
-Category (Phone,Tablet,WiFiCard,WallCharger,CarCharger)
-IMEI
-SIM (This can change)
-Make (LG,Samsung,Apple)
-Model (Galaxy,Spectrum,iPhone)
-PhoneNum (This can change)
-Carrier (Verizon, AT&T, T-Mobile)
-LineStatus (Active, Suspended)
-Notes



I'm not sure if I am on track or way off track.
What do you guys think?

[orange]

It appears that you will need some Lookup Tables to facilitate consistency with Category; Make; Model; Carrier...
If Offices are in different physical Locations, you'll need a Location table.

Here's a link that will help you with table design, Normalization and creating relationships.
http://www.rogersaccesslibrary.com/T...lationship.zip
Go through it to understand what he's doing, then try it with you own data. I recommend you write down the business facts (similar to his starting paragraph) and work through the tutorial.
There are some free data models at http://www.databaseanswers.org/data_...all_models.htm
that may help.
Good luck.

And remember Excel and Access are very different. Your biggest challenge may be to forget enough spreadsheet in order to understand relational database.

[wakerider017]

Thanks!

That was a big help with getting me started! Here is what I have so far:




How does everything look? All of the relations were formed when I used the lookup wizard... Is that okay?

Also I created an 'OrderItem' and 'Order' table. The Order table is supposed to contain 1 to many OrderItem records. (Multiple items in an order). Does it look correct?



----------------------------------------



I'm not sure how all this should look on a form for easy data entry....


Basically I want to be able to edit:

EmployeeT: FirstName, LastName, Email, OfficeName, Notes
StatusT: TrackingNum
OrderT: Satus, OrderDate, Notes
OrderItemT: OrderQuantity, Notes
DeviceT: Category, IMEI, SIM, Make, Model, PhoneNum, Carrier, AquiredDate, Condition, LineActive, Price, Notes

The part I am most confused about is that some order will have multiple items in them and I'm not sure how to accomplish doing this on a form..



Hopefully I am on the right track and please do not hesitate to criticize my work. Thanks!!!

[orange]

Some issues. When you say Lookup Wizard are you referring to a Lookup field in a table design? Or something else such as building a separate table to be used to look up other attributes? Please describe this in more detail.

Take a look at this IT Assets model http://www.databaseanswers.org/data_...sets/index.htm
and notice how the AssetID and EmployeeID are used in table EmployeeAssets to relate back to the Asset table and the Employee table. The value of the Primary key in each of those tables is placed in the EmployeeAssets table as a Foreign key. This identifies the relationship between the tables. Always use the keys to establish the relation eg use EmployeeID, not EmployeeName.

Your relationships ("lines") between tables should indicate some cardinality. That's an indication of how many A's are/maybe related to B's. eg One Order may contain 1 or Many OrderItems.

Here are some videos that should help put more of this into context.
These are quite good for learning by Watching/Listening rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming/cardinality

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

Good luck.

[wakerider017]

What I did is create separate tables such as OfficeT to store all the pertinent info.

After that was complete, I created the table EmployeeT. I created a field named OfficeName in the EmployeeT table. I then changed the data type (Where you can select text,memo,number,etc.) to Lookup Wizard. From the Lookup Wizard I added both the OfficeID and OfficeName from the OfficeT table. I clicked to hide the PK and clicked finish. Now in the EmployeeT table datasheet view I am able to select from a dropdown list of office locations.

Pretty much all my relations have been setup using this method. Is this incorrect?

[orange]

My suggestion is to remove all Lookups from the table. This "feature" introduced by M$oft has not been a hit with developers.
Here's why http://access.mvps.org/access/lookupfields.htm
Instead, the traditional approach is to build another table. Using your EmployeeT and OfficeT as example, you build these in design view as separate tables. EmployeeT has EmployeeID as PK, OfficeT has OfficeID as PK - and usually these will be autonumbers (unique identifiers for records in these tables). You add a field to EmployeeT called - OfficeId data type Long Integer. This field will serve as the Foreign key in the relationship between EmployeeT and OfficeT.

Use this sort of set up for all of your lookup tables.
Your naming convention looks good -- no embedded spaces in field/table names.

Once you get your tables designed, I'd recommend you establish referential integrity by explicitly defining relationships. This will allow Assess/JET/ACE to prevent orphans.

Seems you may have been watching some Richard Rost (599CD) videos with your T suffix on tables. Richard does good stuff.