Designing my Database 1

[cmstforr]

Hi,

I am in the process of designing my database. At present I am determing the fields I need within each of my new tables. My database will store data for hardware audits. I have a number of customers that I carryout hardware audits for.

So far I have identified two important tables. These are:

1 : Customers TBL (Contain information about each customer)
2 : Audits TBL (Contain information about the hardware which is being audited)

I have identified the relationship between these two tables as I know that 1 customer will have many hardware items to be audited.

What I have a problem with is repeating information in my audit table.

The fields that I have identified so far are:

1, ID
2, Asset_No
3, Serial_Number
4, Equipment_Type **
5, Make **
6, Model **
7, Memory **
8, Op_system **

Please note that the fields with ** after them are the fields that I believe will repeat in my table i.e a computer may have the same memory (512) in a number of records.

I don't know if I should remove these fields and create there own tables i.e. memory.

Am I getting a little confused here, if so can someone guide me along the correct route.

Hope you can help!!!!!!!

Cheers,

Tom

 

[zemp]

I would create separate tables for each of the items (memory, OS, make, etc.) and place a foreign key to each item table in the audit table. Look into database normalization.

This way an idividual item will appear only once in your database. It saves space and makes changing the item details much simpler.

zemp

 

[johnherman]

I see three tables: Customer, Asset, and CustomerAsset. The CustomerAsset table links Customers to Assets and is called a bridge, relation, or associative table.

This design allows for an Asset to move from one Customer to another and track its movement. If this is not an issue for you, then you may use a simpler design.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[cmstforr]

Thanks for both replies. Much appreciated.

Johnherman - Am I correct in thinking that when using your method the user will add customer details to the customer table first, then add the audit details (hardware details) to the customerasset table (using the customer as the parent table). Wont I need to also have a number of tables for the memory, operating sys etc as this information will be repeated.

Thanks and kind regards,

Tom

 

[lespaul]

You should read 'The Fundamentals of Relational Database Design'



Leslie

 

[cmstforr]

Hi Lespaul,

The article looks great. I understand to an extend what I need to achieve. I was just looking to see if I was going along the right track or if I was going off track.

If anyone has got some tips and tricks regarding this subject, that would be great.

Cheers,

Tom

 

[lespaul]

My database will store data for hardware audits. I have a number of customers that I carryout hardware audits for

Ok, starting with this information. You have customers and they have hardware. Do you perform multiple audits for each company? Do you need to keep the information on each audit or is the information replaced each time? I would assume that you want to keep past information.

In this situation, I could see you having the following tables:

tblCustomers
CustomerID
CustomerName
CustomerAddress
etc. (anything that has to do with the customer)

tblHardwareTypes
HardwareID
HardwareDescription
(a list of all possible Hardware that a customer may own)

Here is a link to a discussion on how to keep track of all the different aspects of hardware Thread700-860190

Now you want to keep track of audits. What kind of information do you gather when you perform an audit? The AuditID, customerID, Audit date, do you need to enter detailed information for each piece of hardware the customer owns? then you would need an AuditDetail table with a FK of AuditID.

The article looks great

The article is one of the most referenced guides to building databases on this site. You should UNDERSTAND exactly what you need to achieve, not just "to an extent". There are no tricks to achieving a good database design other than understanding your system. We as other professionals can offer advice and suggestions, but you are the one that will be responsible for how the database works.

Now I realize this is just for you, but this won't be the last database you design and unfortunately there are thousands of mission critical databases that were developed by users who "understood to an extent" what they were supposed to do and then the company paid someone else thousands of dollars to "fix" it. If you design it correctly the first time, you will be a lot happier in the future.



Leslie

 

[ceh4702]

You may need to know the vendor and the manufacturer of the products. It wont help with the actual product inventory, but it might help to know how you got the product. You may also want to know the date the product was acquired, the Labor and warranty date, the type of insurance, and maybe an inventory class to make products easier to find. If you record the location bldg room of a product a list can be made up by bldg room for inventory. We use a location for when we send items in for repair. You could use another location for lost or stolen items.

We write insurance for our computers when the warranty expires so this is an important data element to know. If you depreciate big ticket items this may be important to know.

Also some assets are leased and that might be nice to know. We also keep track of items that have been deleted and have a field to indicate that.

We use batch proceesing for inventories. If an inventory item is changed in status or location, etc, there is an indicator so we can print out a list of changes. The action code is also use so we know what the action is. When the inventory is performed we have batches for changes, adds, deletes, etc. If you want a list of new items added in a given inventory or accounting period you can find it in this way.

If you do not like my post feel free to point out your opinion or my errors.

 

[ceh4702]

If you need to know information that is only applicable to a certain class or type of item like a computer you may want a separate table for computer specifics like CPU maker, type and model and speed, hardware components (ie cdrw size of hard drive), Memory type and speed (ie DDR400 or PC3200), computer case style, desktop or server, OS/Version, Software licesnse numbers.

So if the inventory has stuff like tables and chairs this computer informaton is usesless. Having a separate table for specialty items like computers can come in handy.

We have MIRC barcode stickers on all of our computers with the actual inventory item number printed on them. We use this for all expensive items, even tables and chairs.

If you do not like my post feel free to point out your opinion or my errors.

 

[SQLSister]

I don;t get the imression that you understand the idea of the three tables, Customer, Asset, and CustomerAsset, very well. Johnherman's suggestions is a good one. What you have is a many to many relationship between Customers and Assets, that is why you need the Customer Asset table to tie them together.

Look at how the data would actually go inther and you will see what I mean.

Customer
CustomerID, Customer Name, Etc.
1 John Smith
2 IBM

Asset
EquipId Manufacturer PartNo Description
1 Dell Z-867 Xeon processor
2 Harmon GT-345 Speakers

Customer Asset
CustomerID EquipID DAteInstalled Warranty Serial Number
1 1 10/2/2004 1 year 123-4556
2 1 8/25/2004 1 year 098-6789
1 2 10/2/2004 n/a n/a

So the customer table shows details about a customer, the asset table shows the physical details about the types of equipment and the customerAsset table shows details about the relationship of the hardware to the customer.



Questions about posting. See faq183-874

 

[ceh4702]

If you want to join CustomerID and the HardwareID it would be ideal to include the sales data like date/time of purchase or Add-On Warranty Info. This would be good from a sales point of view. Of course you can have multiple sales items per a given sale and make them part of a sales orders with a number for the sale or invoice and incorporate the line items.

If you do not like my post feel free to point out your opinion or my errors.