Is this a decent start to a database design?

[changedsoul]

Ok, so I have managed ti learn a bit as I go along on designing a database in access. Normalization makes sense to me when I watch the tutorials, but eludes me when I try to put it into practice. Can someone give me a hand with my design? I uploaded pic of my tables and relationships and was hoping someone experienced could take a look see and help me make changes if any are needed.



Its a inventory database which I still need to create a front end to. Basically I want to store what we have in terms of serial numbering everything and the locations they are are all stored in. That way when an employee comes and asks for a tool, the person managing the tool inventory can call it up and know right where it is to get it. The data base will also log which employee gets a tool, and which employee returns it. Also how many they took out and or how many were returned.

I dont know yet how to do the quantities. I was thinking doing it through calculations based on the quantity being taken and returned. An initial quantity level could be set when the item is added to the database as a return and a quantity.

Can someone help me with things I may have over looked, what pitfalls I might have the way its it now, etc.

Thanks.

[orange]

I recommend this tutorial. You have to read the business overview, then work through the sample. The author provides some templates and a solution.
http://www.rogersaccesslibrary.com/T...lationship.zip

Once you've tried and mastered the essence of the tutorial -- try setting up your own situation and working through the process.

Good luck.

[ssanfu]

My thoughts:

Naming convention good - no spaces and only one special character. The table "Transaction_IN-OUT" has a hyphen in it. I would have used an underscore.

Table "History" - History of what? I might have named the table "TransHistory" (history of transactions). This is the only table without a primary key???

I am back and forth on the tables "Cabinets", "Drawers" and "Location". If you add a new cabinet or a new drawer number, you have to create many records in table "Locations". If you stored the PKs from "Cabinets"& "Drawers" in table "Equipment_Inventory", there would be a lot less records to enter.
But your method is valid also. Still on the fence about this, but leaning toward the two PKs in the location table.


The big question I have is:
Say there are 20 Air Hammers in inventory. I want to check out 5. The 5 air hammers you give me have serial numbers 4008, 80286, 6502, 8088 & 4004. You enter that I checked out 5 air hammers. Does it matter which 5 I check out? If so, how do you record which 5 I get?

You select 5 tools from inventory, but enter a quantity in the history table. 5 tools can't have the same serial number......


Say Joe checks out 2 air hammers. I break one of my air hammers and, while Joe is on break, I swap my broken air hammer with one of Joe's (don't tell Joe) and let Joe turn in the broken tool. Does that matter?

(BTW, I really wouldn't set Joe up like that...... well, I probably wouldn't)


My $0.02 worth.....

[changedsoul]

The History table I left without a primary key because when I made the relationship to Equipment_Inventory it would not allow a One-to-Many, only a One-to-One. Wasn't sure what to do about it so I removed it.

I never thought about really tracking which item was issued or returned. Its a good point about setting up joe though. That type of behavior is most likely going to happen. There are many items though that dont have a unique serial number. Things like Drills. All I would have is a size and type or something and how many of them. I think for now Ill look the other way when someone sets Joe up.

Thats a good point about the Cabinets and Drawers. When looking at it I had not thought about it that way. This is why I asked for input. I like the idea of putting the PK's for Cabinets and Drawers in the main Table. I think this makes it easier also when entering the data where the items are located.

Thanks.

[changedsoul]

Hello again. I made some changes and would like some guidance on how to go forward. I think I have the data base laid out pretty well. When I was starting to think how a user would interact with a form, I realized I might need another field in my Equipment_Inventory table. My problem is the extra field is only really relevant for a particular type of tool.

For Example
This will be for my company machine shop I work for. Tooling that will be categorized will mainly be inserts, drills, reamer, collets, etc.
In the tooling_types table I list all the types for example, Inserts, drills, Reamer, etc.
In the tool_materials table I list what they are made of, for example Carbide, HSS, Ceramic, Diamond, etc.

I came to an issue when thinking of a user looking for a type of insert. Drills and reamers and collets etc are pretty much what they are, but inserts have sub categories to them....Turning, Threading, Grooving, etc. To make it easier to locate in the table it would be nice when a user selects "Inserts" to have it show a list of what kind of inserts are available.

So Im asking for help on how I could implement this extra info. I could just put another field in the main Equipment_inventory table, but then its not specific to each type of Equipment_Inventory. So I could create a separate table for just Insert categories and link it to the main Equipment_inventory table . Or then there is the third option which I dont know what it is and was hoping for guidance from the great people here.

Can someone give me a hand?