Software Inventory Database Design


I am having a problem designing the tables for this database where there are fields that aren't empty or records duplicated.

I have a software table

tbl_Software_Product

Software_info_ID PK
Manufacture
Product
Type

Here is my problem now: a software product may or may not have a size and may or may not have a version --example data:

Manufacture: Microsoft
Product: Windows XP
Version: Pro SP2
Size: 32 Bit

Manufacture: Adobe
Product: Reader
Version: 10.1

Manufacture: Creative Labs
Product: SoundBlaster X-Fi

I want to create the tables so that I don't have duplicate data and avoid having empty fields or nulls
and suggestions would be great.


Sponsored Links:



Hello,

I am a relative beginner with MS Access. We are trying to create a database of all IT equipment in our large-medium size organisation. I have read about database design a bit and played around with the program, but I am facing a number of challenges:

There are different types of equipment eg computers; monitors; printers; projectors etc. These all share some crucial fields such as manufacturer, serial number, color, model number etc. But they also have unique fields: what is the diagonal size of a monitor? Is a projector lcd or dlp? Is a printer capable of double-sided output? How much RAM does a computer have? That is why I have kept separate tables for each type of equipment, with relationships to lookup tables such as "Manufacturer" and "Location". I hope this is the right approach.

But lookup tables and relationships do not solve all my problems. I NEED the database to 'understand' that there are not printer serial numbers and monitor serial numbers, there are just serial numbers and it is a common field throughout the database. BUT when a user enters a new serial number in the Printers table, I do not want them to have to enter it in a Serial Numbers table first, then enter the serial number again in the Printers table. I only want them to enter s/n once.

We have our own Inventory Number system. They are unique and hand written discretely on every item. Just like serial numbers, the database must recognise these as a 'common thread' that runs through the whole database. There should logically only be one "Inventory Number" field, but it must be represented on every table.

Should the Inventory Number be a DBID replacing the auto-generated one?

LATER EDIT: I suppose I was asking 'should I make our Inventory Number a primary key'. And the answer is that the Inventory Number is a candidate key. However since people can make mistakes like accidentally writing the same Inventory Numbers on two different pieces of equipment, they sometimes need changing. So it is better to have a separate auto-generated primary key.

I want to be able to run a query that pulls in all inventory objects and lists them with their shared attributes such as their location, inventory number, manufacturer, serial number etc.

Such a query I would want to be able to sort first by location/room and then by item type. This makes it easy to walk round and check the inventory is correct. To sort by item type that would need to be a field, would it not? But in the computers table, type is always computer. In the monitors table type is always monitor. It would be tedious to have to enter "type: projector" every time you entered a new projector. Can you get Access to recognise that "this is the projectors table, everything here is a projector"?

How would you design this database?




I like to think that I have a basic understandings of relational databases. I have worked with Access quite a bit; over the last several years I have written a database to run my landscape company including proposals, job database, daily route sheets, time tracking, employee time cards, billing and export invoices to PeachTree, etc.

But now that I am starting to begin the equipment inventory / maintenance portion of the database, I have come to a design question and I am stumped. I have been thinking about this here and there for months and just don't know what to do. And searching for "nested inventory database design" and variations of that and other terms keep bringing me results about nested tables, reports, etc. which is not what I am after.

I am trying to get my head around the best way to design the structure of the inventory part of the database. We have many many pieces of equipment, and I would like to have some levels or containers in the database, such that individual pieces of equipment (and even sub parts, like an engine on a mower, of which we have spares) can belong in or be assigned to a container, or level, something like the following:


Truck 1
Toolbox 1
ScrewdriverHammer
Trailer 1
Mower 1
Engine 4
Line trimmer 6Edger 1Blower 5
Shop (Shop Equipment)
Toolbox 2
Air Impact GunDial Caliper
Floor JackAir Compressor
Shop (Motor Pool)
Line trimmer 8Edger 9Mower engine 6 (spare)

I would need of course to be able to reassign locations periodically as needed. Also, I would need to be able to print or display some kind of report periodically to make sure nothing is missing. I would like this report to be somehow nested, and if possible indented accordingly as above. Some kind of tree view control would be awesome, but I'm afraid programming that is beyond my abilities and so that is not a requirement. But this is where I am stumped on the structure / design of the fields and tables to achieve this result, particularly on a report.

This database is in it's design phase, so I could implement just about anything. But I am looking for best practice, and of course simplest is best. There must be some elegant / simple way to do this, I just can't get my head around what it is.




OS: Windows 7 Home 64 bit
Version: Access 2010

Current design:
(note: * indicates a primary key)

tblInventory - *InventoryID (Auto), ItemID (text), CategoryID (text), SupplierID (num), CategoryID (num), InStock (num)
tblSupplier - *SupplierID (Auto), SupplierName (text), PhoneNumber (text), Website (Hyperlink)
tblCategories - *CategoryID (Auto), CategoryName (text)

I have a simple inventory database for medical supplies. My lab is behind technologically in the sense that everything is still done by hand and there's way too much paperwork involved. There's redundancy in almost everything we do. Every time someone wants to order something, they bring the box with them and write down the item name, catalog number, supplier name and quantity to order. The order log is as thick as an encyclopedia and there's no way to track who requested to ordered each item.

Most of my coworkers are afraid of computer usage and up until now I've been using this database by myself.

This quick reference database has up-to-date stock quantities for all items we carry and this helps me keep tabs on our inventory in a more efficient manner. I print out a report list every Friday and carry it around with me as I determine what needs to be ordered. Rinse and repeat weekly.

I've got continuous forms based off of queries. The forms have a couple of buttons (using OnClick with VBA event procedures) to view/sort/manipulate data then to print reports based on this.

I also have a frmSearch where the user can search and further change the view output of the continuous form based on their search txtstring.

For example someone can search for "scalpels" and return only the scalpels in the database in a continuous form layout, then click "Generate Report" to view the report of only scalpels and then print.


NowI want to add some more functionality and more complexity (with VBA?). I would like to expand this to also make the ordering process more efficient.

Goals:
User login tblOrders, qryOrders, frmOrders Each user can browse the list of items and click a button to order an item (one item at a time, for now) Once the button is clicked (and the user confirms their choice) it will append to a table that summarizes all orders for the day I should be able to know who ordered what and when they ordered it.


Future development ideas...

I would love to have orders for the day/week automatically emailed to the supervisor by 5pm. All orders placed after a time cutoff would append on the following day's order. Its purpose is to let the supervisor know ahead of time what to expect as far as invoices.


Any suggestions for some VBA commands for the buttons on the ordering form? I'm imagining a small popup form after OnClick to confirm item and quantity.




Thanks,

-Mark




I am designing a computer inventory database and currently have a PC table linked via a one to many relationship to a Peripherals such as printers etc table. This works fine, but I have a disposed check box on both forms should we get rid of a piece of kit. The problem is if a PC is linked to a monitor and the PC breaks, we will want to dispose of the PC but keep the monitor. So when we click on the disposed check box on the PC record, I want to relationship between the PC and monitor to break. And similarly, if things were the other way round, I would want to be able to get rid of the monitor, click the disposed check box and break its link with the PC. Is this possible?

Thanks in advance.
Lee




I have some experience with Access including programming in modules. But my background is architecture, not database design, and I'm stumped by this design problem.

I'd like to set up a simple database for inventory tracking. I have three basic tables with the following fields:
1) purchases: purchase date; category; stock description; quantity; cost
2) inventory: stock description; quantity; Ebay venue quantity; store venue quantity;
3) sales: sale date; stock description; quantity; venue

My process would be to enter purchase data, and have the stock description field be the field that allows me to track inventory. So the idea is that I enter data in the 'stock description' field of the purchases table form, and have that same data appear in the inventory table/form.

What is the best way to have 'stock description' data 'populate the inventory table, in such a way that when I'm done entering data in the purchases form, when I go to the inventory form, I can see the same stock description and quantities I just entered, and assign quantities to the two different sales venues.

Caveat: I can have two or more different purchases having the same stock description. So I would need a 'sum query' to show the sum of all the quantities of a given stock description purchased to date.

Any help would be appreciated.

Marcello




Soon, I will be starting an access database to inventory an entire building's IT assets which include computers, monitors, laptops, tablets, servers, switches, taclanes, etc...

All of these items have Serial#'s that are.... quasi-unique... We are moving 10,000+ items to a new building soon. We want to do this in an organized fashion. Since we already have a sorta accurate inventory of current assets, we were needing a way to provide new asset locations w/o problems ("where's my 3rd monitor duuddeee???").

We we have data like this:

(PK) ID: Autonumber
Serial#: Text (Unique)
Type: Text
Model#: Text
Vendor: Text
Price: Currency
Owner: Text
Account Number: Text
Date Acquired: DateTime
etc...

None of this was relational due to lack of ability... I made it relational which is not necessarily reflected above..

Anyway, the IT manager recently took an entry-level database class and is calling shots on how the database will be built. Every time I spoke of anything like 1NF or non-key attribute, etc... you know, BASIC database design, he would go into another 4-5 minute spiel about how he would walk around and scan things and it would "update" in the database. And I couldn't get him to see eye to eye.. I tried to say things like "if item A moves, it's primary key would ALSO have to change" and he would come back with things like "ITEM A SHOULDN'T MOVE WITHOUT MY PERMISSION AND IF IT DOES I WILL UPDATE THE INFORMATION IN THE DATABASE".... Anyway, I don't think he'll notice that I will still design it my way (haha) but there's one particular thing I had a question on...

Scenario: The IT Manager wants to make it easier to locate assets in the building. We are going to track an asset's Serial #, Desk #, Room #. There are many rooms in the building so Room# is a given. However, there are many assets in the rooms (some rooms have 50+ computers). So identifying a station/desk for an item will be VERY useful..

The IT Manager came up with his own control number for this.
Room #: Standard building room number like 11061. Each room # is unique.
Desk #: Room # + object type + serial numbering such as:
11061-Desk1
11061-Desk2
11062-Desk3
11062-Desk1
11062-Shelf1
11062-Rack1

So each desk/shelf/partition/communication closet/etc... will have a hand-made barcode like that. I told him that those barcodes aren't classified as unique identifiers. If a desk changes rooms, its barcode must change. If a desk is moved inside a room, its barcode must change (the 1, 2, 3 helps ID where they are in the room). So I would have to say that the above scheme for labeling/barcoding workstations/desks is INCORRECT because the unique key is not permanent to the object...

So I told him we should INSTEAD print labels with prefix+autonumbering like:

Item-00001
Item-00002
Item-00003
Item-00004
Item-00005

These items will be stored in a separate table with additional metadata like:

Room: 11061
Area: North-east corner.
(PK) ID: Item-00002
Description: Blue Workstation desk.

Room: 11061
Area: First desk on left.
(PK) ID: Item-00009
Description: Blue Workstation desk.

Room: 11061
Area: North-west corner, 2 desks down
(PK) ID: Item-00045
Description: Metal rack

Room: 11070
Area: 2 racks to the right
(PK) ID: Item-00144
Description: Server rack for Network1 devices only

Anyway, I thought that creating a table query for locations would aid in identifying the 4000+ pieces of furniture that we'll be "storing" the IT assets on with barcodes...


Please let me know if it would be a good idea (for an IT Asset inventory database) to control non-IT items for the sole purpose of maintaining data integrity of those non-IT items... If you can convince me with some smart verbiage, that would be great as well!


Thanks.




I work for a university and I am setting up an Access Database to track locations of computers and other items. There are a few tables and I will explain what is contained within each one.

Main Inventory/Location Table contains [Inventory]:

Unique University TAG Number
Serial Number
Building Code
Room Number
Station Number
Item Description
etc...

Table which contains information on each computer [SPECS]:

Unique University Tag
MAC Address
Processor
Memory
etc...

Station I.P. Addresses [STATIONIP]

Building Code
Room
Station Number
I.P. Address

Question:

The goal is to have a query that will show each station with the items located at its station along with the computer's specs and the I.P. address.

Currently I have a relationship set up from [Inventory] to [SPECS] using the University TAG Number. Also I have a relationship which relates the [Inventory] to [STATIONIP] by using the Building Code, Room, Station Number.

The problem is that this obviously associates an IP address to items which are not computers, such as monitors and projectors. Brainstorming I realize that items should only have an IP Address if the University TAG for the item is on the [SPECS] table. Is there a way I can do some sort of conditional relationship so that it only relates the I.P. address to the [Inventory] if the item is contained on the [SPECS] table? I assume this would involve visual basic coding.

I posted in the section in case someone has an idea for how to change the structure of the database by changing tables or anything that would allow a better end result. The main idea is to have the location for each item be in only one table. Also the I.P. addresses are strictly set no matter what computer is at that station, this is why I have these in a separate table.

MAC Addresses are unique to each computer but could from time to time be changed if a network card fails, so I wanted to keep it in one table as well, however maybe something clever could be done with changes cascading. I have tried thinking up several different ways to setup these relationships to make this query work well. Struggling I come to you for help =]

Thank you in Advance!




I do some access programming for local non-profits. I have always charged by the job, but typically put the hours on my estimate. I recently completed a job in less than my estimated time, and the customer asked if I would reduce my final invoice.

I'd like to get some other opinions on whether other database designers charge by the hour or by the job.

Thanks!




Hi, i'm creating an Inventory database to record hardware components of computers in an office, the database allows you to add multiple HDD, cd drives, peripherals etc but when it comes to doing a report on specific computers it shows reports for each combination of multiple hardware devices, if anyone has any ideas can you please email me at watoom@hotmail.com and i can send you a copy of my database to look at.
Cheers
Garth




Goodmorning all,

I work at a sawmilling company down here in New Zealand and we are looking at getting a new database designed rather than buying an off the shelf access system.

The timber products that we sell are made into packs (bundles).

Each pack has a unique number.

Each pack is made up of a certain amount of boards (depending on board size) that have the following criteria:

Grade
Treatment
Dryness
Finish
Nominal Width
Nominal Thickness
Actual Width
Actual Thickness
Length (3.0, 3.6, 4.2, 4.8, 5.4, 6.0, 6.6, 7.2m etc)

An individual pack usually has boards at several different lengths.

This is all fairly straight forward to put into a "Pack Details" table but where my design concept struggles is length critera.

Here in New Zealand we use the metric system and 99% of the time our sawmill produces, and our customers order standard lengths that can be allocated in a table as above using a new feild for each length. We have expressions that work out invoice calulations based on these lengths such as total peices, total meters, total cubic meters which is the amount of peices x length x nominal width x nominal thickness.

The problem comes when very ocassionally we get and order or produce a product that has a different length than we have allowed for in our pack details table and related invoice expressions.

What I need is some way to accomodate a couple of user definable length feilds in the database so that we can use our standard lengths most of the time but add one or two new lengths if required. This would also need to translate to all the expressions in the database.

Has anyone got any advice here?

At the moment I have all the pack details, including length in one table but I am not sure if this is the way to go as lengths and there related expressions are very fixed this way.

Any ideas would be appreciated.

JG




I am getting this error message when I try to link my Access 00 front end to the Access 00 backend across a network share, "Microsoft Jet Database Design cannot open the file "Access_mdb". It is already opened exclusively by another user, or you need permission to view its data".

This frontend was and has been working for a couple of months but now this is what I get and it won't let me relink.

Here are some facts about the problem and what I've done:The backend is not open when i attempt to re-link tables across the network share. I am not using a Mapped network Drive for the link! There is no record locking file present in the folder with the backend to stop me from accessing it Sharing is set to shared under database options The folder permissions where the backend resides is set to full sharing for EVERYONE When I move the frontend and backend to another folder I can link tables from the second workstation from that frontend Full sharing is setup on the "main machine" from the C: and the Folder containing the frontend/backend I have tried this also by disabling the Windows XP Firewall to no avail
I have spend two days searching everything access and xp permissions related and I have reached my limitations on this. I would appreciate any and all comments to help me try and resolve this matter. Thanks in advance for any and all suggestions.




Folks,

If you have info about an online website where I can find database design and data entry, analysis work please share the link with me. Your assistance is highly appreciated.

Regards,
Hamdard




Hi folks,

I have 11+ years of experience in database design and data processing. If you know of a link/site that offers online contracts in database design and/or data processing, please let me know.

Thanks for your assistance in advance.

Regards,
Hamdard




I'm new to this forum. I'm wondering if I can have someone critique my database design in a forum. Or are the forums for more specific questions? If this is not the place for this, can anyone recommend where to go for this? Thanks.




Hello people I want to ask you smart people for some advice.

Basically I am about to start designing an access database for a courier company. I have looked at other databases systems (like the northwind database that comes with access). They all deal with ordering and supplying and all the complex stuff.

The database that I will be designing is pretty simple as in it will only have four tables. Customers, Employees, Vehicles, and the main table JOB DETAILS.

Customers and employees will be linked to JOB DETAILS and employees will be linked to Vehicles.

I basically want to know if this is the right way of starting off the design.
What I want at the end of the design is a database that I can constantly update with new jobs daily. When a customer has a query regarding a job we have done i want to be able to look it up with no problems. And last and probably the most difficult thing I want the database to do is invoices at the end of the month. I know that you can generate an invoice report from a query which is the way I will be doing it however i do not know how to go about setting it up in a way so I can see who has paid and who hasn't. In the job details table one of the fields will be "Invoice paid" with a check box. I don't know how to sort out a way of seeing who has paid or not.

Someone please give me some pointers I would be most grateful.

One more thing I will be working on this database design at home and at work is there a way that I can save what I have done at home to take it into work? As i did try to save it to floppy disc and it was filled up to quick. Can I put a database onto a number of discs??

I don't want to work on it at home and then have to do it over again at work it will take longer that way. If there is a way please let me know. Probably sounds like a stupid question to you but I am relatively new to computers having just passed my C.L.A.I.T.

Thankyou for taking the time to read this thread and any suggestions will be gratefully received.

THANKS
CLARE aka SWITCHWORK




Hi,

Can anyone look at this database design and help me out.The purpose of this database will be to store race results for drag racers this will include specifications of there cars, car setups, engine specs, track conditions and weather conditions. The main people to use this database will be drag racers.

Everyone is free to use or modify the design.


tblEvent
EventID
EventType
EventDate

tblDriver
DriverID
FirstName
LastName

tblCar
CarID
CarMake
CarModel
EngineMake
EngineSize
Gearbox
DiffRatio
TyreType
TyreSize
TyrePressHot
TyrePressCold
CarbyMake
CarbySize
JetsFront
JetsRear
FuelPressure
BaseTiming
TotalAdvance
EngineCompression

tblCarDriver
CarDriverID
CarID
DriverID

tblWeather
WeatherID
RaceTime
TrackTemperature
Milibars
Humidity
AirTemperature

tblResults
ResultsID
CarDriverID
StageRpm
ShiftRpm
Reaction
60ftTime
330ftTime
660ftTime
660ftMph
1000ftTime
ElapsedTime
ElapsedMph
Comments


Any info would be greatly appreciated




hello, i am looking for a music inventory database, anybody any idea where i might get one
cheers
phil




Greetings.. I am attempting to teach myself Access but I am having trouble trying to figure out how to implement into my database the features I need my enduser to have.

Basically I am trying to create a Contacts database and I want the "form" that my end user enters data into to look like this (all examples are minimalist because I am looking for specific logical answers to solving my problems, not field suggestions):

Name: [textbox]
Phone: [textbox]
Email: [textbox]

Now I need to have Phone and Email each in their own tables, or in some way seperated so that I can retrieve a list of emails or phones without duplicates.

My initial attempt was to create the tables:

*key

NAMEtable
*Name
Phone (related to PHONEtable.Phone)
Email (related to EMAILtable.Email)

PHONEtable
*Phone


EMAILtable
*Email

Now the problem with that setup is that I cannot create a new entry into NAMEtable unless the specific Phone AND/OR Email are ALREADY listed inside their respective tables.

What I need to be able to do is give my user the ability to use a "form" with 3 fields, that will do the following:

Allow a new Name without a Phone or Email.
Allow a new Name WITH a Phone and/or an Email.
Allow a 2 different Names to have the same Email, but pointing to only 1 instance of that email in a different table.
(which it already does all if the Email and Phone already exist in their tables, I need it to be able to create new entrys into those tables from the form if they don't already exist). I also need to be able to enter in new Emails from a different form2 that dont have any relation to any persons in Name (which isn't a problem either at the moment since I just open the Email table and enter data).

I am unsure how to execute this first forms dilema though, I am assuming my database design is fine and it has something to do with some formality or code that I am missing to be able to do what I want. If so could someone help me out and make some suggestions? Or if my design is way off and there is a better way to be able to enter and store a Name, Email, and Phone from a form that relates back to a single contact and prevents duplicates then please let me know. Thanks

Also here is another post I made in a different forum but wasn't able to get quite the answers or help I was looking for.


"Name [textbox]
Email [textbox]

if the email the user is entering isnt already in the database it will not create a new row in email.email and then link contacts.email to that specific email.email...........

for example if

contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1

email Email
row1 = bob@bob.com

then I open a form that will have 2 members, both pointing to the same email.

1 of 2
Name: [bob]
Email: [bob@bob.com]

2 of 2
Name: [bobswife]
Email: [bob@bob.com]

Ok, lets say I go to create a new member

I can do this just fine:

Try1
3 of 3
Name: [bobsson]
Email: [bob@bob.com]

What I can't do but need to be able to do is:

Try2
3 of 3
Name: [bobsson]
Email: [bobson@bob.com]

It will return an error stating that the email isnt in the email table. I need to know how to get Try2 to do what I want so that when I enter 3 of 3 my database looks like this:

contacts NAME , EMAIL
row1 = bob , email.email row1
row2 = bobswife, email.email row1
row3 = bobsson , email.email row2

email Email
row1 = bob@bob.com
row2 = bobsson@bob.com"

Anyways.. thanks again for any help.




I need to create a small inventory database tracking product in stock and "x" amount of raw material to create it.

Is there a way, using MS Access that I can track inventory of the product as well as update how much material was used to create the product?


I am new to access but I know how to create a database and enter the fields I want, eg: product id, model number, product color, in stock, re order, lead time.

My problem is associating another table to deduct/or add the amount of material based on totals.

In a nutshell, I want to track product inventory as well as track raw material in stock to create more.

Thank you for any help!