07-11-15, 15:30 #1Registered User
Provided Answers: 1
- Join Date
- Jul 2015
Unanswered: Am I on the right track in designing this database?
I'm just learning how to create an Access database (really not much experience in databases at all) and I'm hoping that someone can take a look at my screenshot here and tell me if it appears that I'm on the right track with my design, or if I'm entirely missing the picture in how a database ought to be setup from the tables standpoint.
I've been trying to self-teach myself a bit here. So far, the general concepts I've found and am trying to implement is that tables shouldn't contain data already found in other tables, although there may be one or two fields that correspond (Primary key and foreign keys).
I believe I know basically what kind of data I need and I've tried to set it up in it's own tables to separate it out in an efficient manner, however i'm having some trouble with the relationships and understanding how they work. For example, I seem to be able to enter incorrect data in a field even though the field it's linked to has validation constraints on it.
I don't know if this screenshot is a good way to ask my question, but basically, from what you can infer from it, can you tell me if it looks like I've got the right idea on this at all?
Basically, it's an asset database that is intended to track the following:
- General Equipment
- What room the equipment is located in
- What employee has the equipment
- What phone extension the employee has
- Other misc. phone/equipment/employee details
Thank you for your time and answers.
Last edited by JDDellGuy; 07-11-15 at 15:31. Reason: Adjusted image location
07-12-15, 10:43 #2Registered User
Provided Answers: 23
- Join Date
- Apr 2014
I don't see why an inventory item is tied to a phone#.
07-12-15, 12:04 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
can an employee have more than one location and or more than one phone number
can you have more than one item form a single manufacturer, iuf so then probably there should be a separate entity for manufacturer
can the same product be supplied by different reseller / vendors
device category screams out as a separate entity
I think you are confusing a product with inventory. a product is a product, there may be 0,1 or more of that product but they may be in different locations. ferisntance a company may have a telepjone system using the same handsets. so the company has handsets but also needs to know where each individual handset is. so Id expect something that defines types of stock items (eg a Cisco IP handset), and then another table that describes specific instances of those items (eg handset X in stores, handset y in reception and so on)
use relational links where possible, enforce data integrity through relational links
is it possible that two manufacturers could separately produce items which happen to have the same serial number
consider using the sub/supertype model to handle category specific features. or, and I hate to even suggest it consider the EAV for non ciriti9cal information
if this is general inventory then is it possible that you may have 'stuff' which isn't connected on networks and so may not have n ip address
be consistent in your naming of columns and tables. either use CamelCase (SerialNo) or Underscore (serial_no), but don't use spaces to separate words. if you must have spaces then set the caption property as part of the tabel design NOT the column name itself
be wary of using reserved words or symbols, although Access does a pretty good job of resolving such probelms its not clever and at some stage it will bite you badly.
you refer to a table called room numbers but have a (presumably) FK called location... consistency. by all means call tables by their plurals (eg Locations) but where the column is used call it location.
do you need to refien your location (ie is a room number sufficient or should it also define buildings, areas etc... not every item of inventory is neccessarily in a room or building for that materI'd rather be riding on the Tiger 800 or the Norton