October 5, 2014 at 8:55 pm
Hello,
I've worked in IT for some time and want to create an inventory database. Many of the turn-key offerings focus a lot on hardware specs (memory, hdd size, cpu speed etc.) and active management of assets. However, I have never found one that includes all of the information I need to easily pull the reports I want and I rarely need hardware specs from inventory or do I need management tools built-in.
Anyway, this is my first attempt to design a database - an area I'm trying to dig into. Attached is the design I created with Lucidchart. If you have a Lucidchart account, you should be able to edit this design here .
Please let know if there are things I should do better or if I just created an useable mess. 😉
Thanks for your help and time.
October 6, 2014 at 1:24 am
Quick first notes (there will be more):
😎
"Users"
1. What attribute or collection of attributes uniquely identifies each user?
2. Users cannot have middle or additional names?
3. Full name should be a computed column, otherwise the same values are maintained in more than one column.
4. Character columns definitions are too short, especially the EMail which often goes beyond 50 characters.
5. Do not use IMAGE data type, it is depreciated, use either varbinary or filetable storage for the images instead.
"Titles"
The relation to a User according to this is that each user can have none or more titles, each title instance being explicit to each user and cannot be shared between users. This is in essence wrong as there is a finite set of titles which can be shared by those users who carry one. Further, a user should only have one distinct title. Reflecting this in the design, remove the foreign key from the Titles table and add a Title_id foreign key in the Users table.
October 6, 2014 at 10:03 am
I agree with everything Eirikur said. His comment about Titles applies to Department, Location and User_Status as well.The same happens with the relations between Equipment and Area, Models, Funding Source, Manufacture and Equipment Status.
You shouldn't use bit columns to define a status, you should use a catalog with actual descriptions, otherwise, you'll get a hard time when trying to show the status.
October 6, 2014 at 10:56 am
First off, I want to thank both of you for making the internet a great place to quickly learn new things and helping others.
2. I will add middle name and alias attributes to the users table. I was thinking about this originally and said I don't need that level of detail for this application. However, that may change in the future and it is easier to add it now that retro actively. Plus it will help when there is a name conflict.
3. I did/do plan on making full name computed, but still need to read how to do that.
4. I will certainly increase character definitions. 100 seems pretty spacious no?
5. Thanks for this info. Will change to varbinary.
@luis Thanks for the tip about using bit type for status. I figured a query for a true or false value would be easy. What you're saying is, I should just have the attribute "Status" in that table with values "Active", "Inactive", etc.
Now on to the part I seem to having the most trouble with. When I first created the design, I only had a Foreign Key in each child table of a parent table (e.g FK Equipment_id in Area table) [one-many relationship from the parent table to the child table]. I changed it after seeing examples of child tables with both a Primary Key and a Foreign Key. After thinking about it, I thought that makes sense if I ever wanted to create relationship between that child table and a sub child table (not likely but...).
Was my original design of a single FK in the child table correct?
Or are you saying that I have the relationship in the "wrong direction"? It should be one Title to many users?
The Equipment, Users, Licensed_Software, and Orders tables should all have a many-to-many relationship to each other correct? And the only way to accomplish a many-to-many relationship is by using a intermittent table?
October 6, 2014 at 11:18 am
That's correct, you could keep a bit column to group status if needed. For example:
CREATE TABLE User_Status(
user_status_id int IDENTITY PRIMARY KEY,
user_status_description varchar(25) NOT NULL,
active bit NOT NULL)
INSERT INTO User_Status
VALUES ('Active', 1),
('Inactive', 0),
('Resigned', 0)
SELECT * FROM User_Status
Note that there's no FK. The PK from this table will be an FK of Users table.
I'm not sure if your Users_Equipment table' design is correct because I don't know how the system will work. It seems to be at least partially correct, but we're missing a lot of information.
October 6, 2014 at 12:00 pm
Luis Cazares (10/6/2014)
That's correct, you could keep a bit column to group status if needed. For example:
CREATE TABLE User_Status(
user_status_id int IDENTITY PRIMARY KEY,
user_status_description varchar(25) NOT NULL,
active bit NOT NULL)
INSERT INTO User_Status
VALUES ('Active', 1),
('Inactive', 0),
('Resigned', 0)
SELECT * FROM User_Status
Note that there's no FK. The PK from this table will be an FK of Users table.
I'm not sure if your Users_Equipment table' design is correct because I don't know how the system will work. It seems to be at least partially correct, but we're missing a lot of information.
This same structure applies to "Equipment Status". Add the "Equipment_Status_ID" to the Equipment table, and make that a foreign key to Equipment_Status.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 7, 2014 at 1:59 pm
Further on the subject, first an example of a user, title and status tables with mandatory constraints and keys.
😎
USE tempdb;
GO
SET NOCOUNT ON;
GO
IF OBJECT_ID(N'dbo.TBL_USER') IS NOT NULL DROP TABLE dbo.TBL_USER;
IF OBJECT_ID(N'dbo.TBL_STATUS') IS NOT NULL DROP TABLE dbo.TBL_STATUS;
IF OBJECT_ID(N'dbo.TBL_TITLE') IS NOT NULL DROP TABLE dbo.TBL_TITLE;
CREATE TABLE dbo.TBL_TITLE
(
TITLE_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TITLE_TITLE_ID PRIMARY KEY CLUSTERED
,TITLE_VALUE VARCHAR(50) NOT NULL CONSTRAINT UNQCNSTR_DBO_TBL_TITLE_TITLE_VALUE UNIQUE
);
CREATE TABLE dbo.TBL_STATUS
(
STATUS_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_STATUS_STATUS_ID PRIMARY KEY CLUSTERED
,STATUS_VALUE VARCHAR(50) NOT NULL CONSTRAINT UNQCNSTR_DBO_TBL_STATUS_STATUS_VALUE UNIQUE
);
CREATE TABLE dbo.TBL_USER
(
UserId INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_USER_USERID PRIMARY KEY CLUSTERED
,F_Name VARCHAR(50) NULL
,M_Name VARCHAR(50) NULL
,L_Name VARCHAR(50) NULL
,TITLE_ID INT NULL CONSTRAINT FK_DBO_TBL_USER_TITLE_ID_DBO_TBL_TITLE_TITLE_ID FOREIGN KEY REFERENCES dbo.TBL_TITLE(TITLE_ID)
,STATUS_ID INT NULL CONSTRAINT FK_DBO_TBL_USER_STATUS_ID_DBO_TBL_STATUS_STATUS_ID FOREIGN KEY REFERENCES dbo.TBL_STATUS(STATUS_ID)
,Full_Name AS (ISNULL(F_Name,'') + ISNULL(' ' + M_Name,'') + ISNULL(' ' + L_Name,'')) PERSISTED
,Username VARCHAR(50) NOT NULL CONSTRAINT UNQCNSTR_DBO_TBL_USER_USERNAME UNIQUE
,E_mail VARCHAR(100) NOT NULL CONSTRAINT UNQCNSTR_DBO_TBL_USER_E_MAIL UNIQUE
,Picture VARBINARY(MAX) NULL
,Start_Date DATE NOT NULL CONSTRAINT DFLT_DBO_TBL_USER_START_DATE DEFAULT(GETDATE())
,End_Date DATE NULL
);
In this case, one can argue whether to use an empty value in the title and the status table to represent an unknown/empty value or allow nulls in those columns.
Focusing on the modelling process, a good starting point is to write a human readable narrative, describing objects/subjects, the properties of those and the relations of those to other objects/subjects. This narrative can then be used as a guide for high,mid and low level modelling. Here is a sample applicable to the Equipment. It is neither complete nor necessarily correct, look at it as a generic example.
The Equipment can be considered being a hardware inventory, inventory items originate from a vendor which either is the manufacturer or obtains the item from a manufacturer or another vendor, normally that far down the chain is irrelevant to the model.
Items belong to a finite list of categories, vendors tend to supply equipment from related categories.
Items normally have a model designation and often a numerical sub designation.
Non-consumables have serial numbers as an identifier. Serial numbers can be shared by different items and must be used in combination with the full model designation to form a unique identifier.
Items can have none or more network interfaces, each which can have none or more IP4 addresses and none or more IP6 addresses.
Most items carry a mandatory one year manufacturer warranty, some items carry different and more or less extensive warranty.
Items can have none or more sub-items.
Items can have software which is essential for the operation of the Item, either operating system or a firmware. The software is of a certain type and has a designated version.
No item is free, all have monetary value or a price. A cost of an Item is not necessarily the price of the Item as the cost can include installation, transportation or other related charges.
None or more notes and comments can be registered for each item throughout it's service life, each at a given date and time by a specific user. Notes and comments are specific to a single item across the scope of it's user history.
Each item has a user history of none or more users, defined by a start and end date for each user which the Item is assigned to at the given time.
Items can be shared by none or more users at any given time, depending on the item usage policies, item category etc..
October 30, 2014 at 7:19 pm
I've been working on this more on and off between many other things, so sorry for the delay. Thank you all for the great advice. I made some changes and included recommendation about user and equipment statuses.
Summary of database goals:
I want the inventory database to have a trail from the moment we order a piece of equipment to the moment it leaves our possession. For example the order number and date, along with digital copy of the PO and shipping invoice; as well as who the equipment was assigned to along the way. I also want to keep keep track replacement parts (RMA’s) on any equipment in the same manner. This will show repair history on equipment. Or if whole equipment was RMA’d not just parts.
One of the most important goals, is to never delete equipment records; rather simply hide the equipment. So when equipment is retired or scrapped, I would like it to have the date it was scrapped and which company/organization picked up the equipment. I was originally thinking of moving those records to a “scrapped” table, but I think simply having a status of scrapped on the equipment record will suffice? That as well as audit tables. This is one of things I find missing from every inventory database for IT. When the equipment is scrapped or replaced due to repairs, it is just deleted and there is no trail or record afterwards (it might get tracked in a Excel document somewhere). When it is time for an annual physical audit you end up trying to remember if that serial you can’t find was scrapped or RMA’d and replaced. This will still occur with a good database, but hopefully a lot less.
One of the more ambitious goals (since this is my first database) is to have audit tables for each table so I can have a history of who made the change and when. For right now I just want to focus on getting the core database working.
Ideally this type of database would probably be linked to a HR database and maybe something like Active Directory to pull employee info; instead of recreating that info in a separate table/database. However, I do not have access to HR data would prefer to keep it that way 😉 Therefore, some of the tables such as the Users table are not as comprehensive as they could be. That it is ok with me. My primary goal is to have enough information about the user who entered equipment into the system, who was assigned what equipment and when, who removed equipment and when to provide a good audit trail. Same goes with attributes in the equipment table - I know there are more fields that can be added, but I just need enough unique data to provide good clues to track down equipment (i.e. MAC, static IP’s, Host name, serial, model, asset tag, etc.).
Here is a link to the latest ERD which is also attached.
I'm hoping I have enough of a solid design to start actually building this database. I'm sure it will change some more as I start doing so.
😀
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply