New MS Access Dbase from Scratch...ERD Help Needed Access File Attached

I've uploaded a zip'd copy of my access file...please check it out and comment.
Thanks!

MS Access has a template for Asset Inventory...I looked at it, but it's more convoluted then what I need/looking for...

So I'm looking at building an asset tracking database from scratch.

I'm at the ERD phase...I fear I'm over analyzing the table structure and I'll wind up with "5th Normal Form" then just a 2ndNF or 3rd (Ideal).

EDIT: The following is kind of Shotgunned...


I'm thinking all devices will have "Service Tag" as their main index

I want to track our pc's and network equipment that I have to care for and repair.

Attributes are:
Type of Device, (Computer, Printer, NetworkDevice)

Printers breakdown to Laser or Ink, Scanner, ALLinOne, Printer
Those also have things like Color or B/W, 10/100, Gigabit, USB, IP Addy

I need the Location....Which Field Office they are in

What user has them? (I figure I can use "CopyRoom" as a user when a device is not assigned to a particular user, but is used by all.

The Computer Breakdown has several things to keep track of

Brand, model, Display type, Acquired Date, Retired Date, Purchase Price, Current Value, User, Previous User, comments...

Related Equipment Service Tags...


I started with a flat file...but there would be too many nulls, etc that I came to the realization that I'll need more then one table...so I've started a couple of ERD's and it's ugly...so had the throwing this to the wind and see what you guys have to say.

Thanks for your help!

-dc

Few suggestions for your question.

A database contains few tables which in turn(typically) have a primary key. For you, the service tag will be primary key for all components(desktop, laptop, printer and so on). Keep in mind that all attributes of a record should not be further divisible. In other words, you can have a computer table which has fields like service tag, Brand, model, Display type, Acquired Date, Retired Date, Purchase Price, Current Value, User, Previous User, comments, but none of these fields should be more divisible. You have that right. Also, there should be no redundancy which you have understood correctly. Have a database called Asset Inventory which has tables called desktop, laptop, printer, scanner, equipment type(this will have fields like service tag and type like laptop, desktop). The use of equipment table will allow you to find what a service tag indicates(desktop, printer). Then. you can go to the respective table(desktop, printer) and get more details on it. Also, think completely what else will you be needing this database for? Will you generate reports? If so, what would be their format? Will you need forms to query the tables dynamically? The more you think initially, it would be better.

You are on the right track, but try a different forum which deals with such database normalization questions like www.utteraccess.com and http://compgroups.net/comp.databases.ms-access/ if you have not already tried. There you can get much comprehensive help as most Access developers are in those forums.

Hope it helps. PM me if you need further help. I can send you some e-books or quick tutorials to come to speed with Access.

Some,
Thanks for the reply.

In previous semesters, I've taken PSQL and then later on Access2K7.

Problem is that I've forgotten a bit of it as our main estimating "program" is a macro that runs on Access, and it's just a front end for it and those classes were a couple to few semesters ago.

I'll go take a look at the linky's you just put up. It's a big help to bang this against someone else...unfortunately I'm the one here that's ever used Access beyond our custom front end.
Thanks.

Quote from .DC. : Some, Thanks for the reply. In previous semesters, I've taken PSQL and then later on Access2K7. Problem is that I've forgotten a bit of it as our main estimating "program" is a macro that runs on Access, and it's just a front end for it and those classes were a couple to few semesters ago. I'll go take a look at the linky's you just put up. It's a big help to bang this against someone else...unfortunately I'm the one here that's ever used Access beyond our custom front end. Thanks.

DC,
Glad I could be of help.

Before posting in those forums, make sure you think through completely what you need the database to do today, and in the future so that you can get the design correct. Because the more detailed/clear your requirements are in your first post, the better(and more useful) responses you will receive on those forums.

For PL/SQL and Access 2k7 there are bunch of tutorials on the Web and on utteraccess forum too.

Hope you get your database sorted out quickly and well.

Quote from some : DC, Glad I could be of help. Before posting in those forums, make sure you think through completely what you need the database to do today, and in the future so that you can get the design correct. Because the more detailed/clear your requirements are in your first post, the better(and more useful) responses you will receive on those forums. For PL/SQL and Access 2k7 there are bunch of tutorials on the Web and on utteraccess forum too. Hope you get your database sorted out quickly and well.

Your 1st post has helped me a lot, thanks.

I so know what you mean by the 1st post...same thing here...my pc doesn't work...um ok...I'd hate to do that same thing and look like the total n00b I'd be.

I wonder if I need a "Brand" table.

I have Dell Desktops, Servers, Printers, Servers.

So the same brand will be on several tables
TblDesktop, Brand
TblLaptop, Brand
TblServer, Brand

So Brand will be common between the tables...It's this lil minutia that gets me worried that I'll be at 5NF!!

Quote from .DC. : I wonder if I need a "Brand" table. I have Dell Desktops, Servers, Printers, Servers. So the same brand will be on several tables TblDesktop, Brand TblLaptop, Brand TblServer, Brand So Brand will be common between the tables...It's this lil minutia that gets me worried that I'll be at 5NF!!

Yes, a brand table would be helpful for your requirement. It can have two fields brandid(number datatype) and brandname(string) which will have values like HP, Dell, IBM and so on.

Each component of your inventory would have a brandid field. Brandid could be primary key for brand table and foreign key for other tables.

HTH

Quote from some : Yes, a brand table would be helpful for your requirement. It can have two fields brandid(number datatype) and brandname(string) which will have values like HP, Dell, IBM and so on. Each component of your inventory would have a brandid field. Brandid could be primary key for brand table and foreign key for other tables. HTH

Yup it sure does!

Thx

Weird issue...In my Site Table (table for where each of our offices are) I want to track their phone numbers
We have several lines for each location...Main # then the roll-over phone numbers, and fax numbers...some locations have 2 or more fax numbers.

The issue is that we generally have the DSL come in on the fax numbers...it's cleaner then having them be associated with the main or the roll-over numbers...

How would I deal with the same data used twice?

Fax # 916-555-1213
DSL#916-555-1213

The data would be an exact duplicate between the fields.

Is there a better way to handle the DSL reference to the Fax#?

*additional Info...some times we are with a cable co provider for 'net service, so the fax # would not be used.

Thanks!

You will need to add 3 tables.
First table call it phoneNumbers with fields (phoneID int, phoneNumber varchar or long)

The two other tables will be "bridge" tables:

siteFaxNumbers (siteID, phoneID)
siteRollOverNumbers(siteID, phoneID)

(you can combine the 2, and add a third field "type" or something)

In your Site table youwill need to put your mainNumber will be a phoneID, fax and roll over numbers will not be on this table. As you will pull that data using joins and the bridge tables.

Quote from mestizo122 : You will need to add 3 tables. First table call it phoneNumbers with fields (phoneID int, phoneNumber varchar or long) The two other tables will be "bridge" tables: siteFaxNumbers (siteID, phoneID) siteRollOverNumbers(siteID, phoneID) (you can combine the 2, and add a third field "type" or something) In your Site table youwill need to put your mainNumber will be a phoneID, fax and roll over numbers will not be on this table. As you will pull that data using joins and the bridge tables.

Sometimes I think I have or am trying to make too many tables...but you make sense to break it up. Thanks...I've got more to ponder