Database Design advice

Database Design advice

• Benjamin Noyce

  You can store the Printer details in the same database but remember the Database is only one part of the equation. your still going to have to write code to link everything together so the further apart you break the data up the more work your going to have to do on the code side to make everything gel.

  What I'll do is work on a comprehensive article. (It's been a while since I wrote one) and send it to you. There I will include some samples and explanations. seeing there is a lot of ground here and communicating in a message post will lose its point. I will also post a link here in the thread so that anyone else thats interested can read it.

  
  

• LPATTERSON

  In response to my last question, I forgot to add one thing. After I have saved the data I want to include the serial number of the item such as a desktop PC into the main table. What fields should I include in the main table after saving this from the application.
  

• Mando

  Ok This all depends on how detailed you want to be with you asset manager. You said you wanted to include Warrenty information as well as Serial Numbers. How about the Users who are using the computer. This can be tied to a Users Table (This table would include all the general User infomation such as First Name, LastName, Position.) Actually I recommend having a user Table and tieing the USERID to the particular piece of equipment. This will allow you to easily track a specific piece of equipment as well as swapping assets between users (and we all know how many times IT is moving computers and equipment between users).

  Anyways. basically I would have my main table set up like so

  AssetID - int (Primary Key is identity)
  ModelID - int (relates to the ModelID field in the Models table)
  UserID - int (Relates to the UserID of your Users Table If you have one)
  DatePurchased - Date
  WarrantyExpire - Date
  SerialNumber - VarChar(length)
  Comments - Text

  Thats The basic information I can think of. Just remember that your Main Table is used to track a single Piece of equipment. Theres no need to include fields for Make Information seeing thats related in the ModelsTable (MakeID)

  some ideas of extending this

  You can use a Department Table instead of users if you just want to track department by department. instead of drilling down to a single user.

  You can add history when equipment is moved or decommisioned which is a little more advanced and would need a seperate table to save an entry every time an asset is moved, repaired, upgraded, or sent to the bone yard.

  But as far as the basics the above is a pretty good outline. Hope this helps.

  
  

• JodyByrd

  Hi:

  Theres one thing about your design that struck me as curious. seeing its an IT Asset Manager, were you also thinking about tracking Networking equipment, and other misc. stuff.

  Seeing that you stated that you didn't want to show all kinds of IDs in the main table I would condsider a diesign like so.

  Ok Keep this in Mind. HP makes both Computers, Printers, and they also have some servers and networking equipment. The same goes with Dell. Having said that you would have to have to insert The Dell Entry into two tables of your existing design. The PC Makes Table and The Printer Make table.

  What I would do is have one table called Makes and have the following fields

  MakeID int (Primary Key is Identity)
  MakeName VarChar(length)
  Computers (Yes/No)
  Printers (Yes/No)
  Servers (Yes/No)
  Networking (Yes/No)

  ok so in the case of Dell where they make Computers, Printers, Servers, and also make Networking equipment such as Routers (I have a Dell 4 port wireless) then a sample entry would be as follows

  MakeID - 1
  MakeName - Dell
  Computers - Yes
  Printers - Yes
  Servers - Yes
  Networking - Yes
  

  now with this structure you can query just the Models that corrospond to the particular equipmet by querying the data base with a where Computer = 'Yes' (for Make Names that make computers)

  another example would be a Generic PC Maker that just builds PC out of their corner stroe (they build great PC's and Server Boxes but don;t build Printers or Other Misc. stuff so an example of that entry would be

  MakeID - 2
  MakeName - Generic Computer
  Computers- Yes
  Printers - No
  Servers - Yes
  Networking - No

  ok hope thats clear now moving on to the models table. seeing that Model Numbers
  we can put all model numbers in this table and tie them to the MakeID that you had before but we will add another field here to say the type of equipment that the Model is (DeskTop,Laptop, Server,Dot Matrix Printer, Deskjet, Networking etc.) and heres how that table would be defined

  ModelID - int (Primary Key is Identity)
  MakeID - int (Relates to MakeID of the MakeTable)
  ModelName - varchar(length)
  ModelType - varchar(length)

  Ok so in the case of a Dell Latatide the record in the Model table would look like so

  ModelID - 1
  MakeID - 1
  ModelName - Latitude
  ModelType - Desktop

  Thats about all there is too it.

  an alternative to this design (depending on how specific you want to drill down) is change the Models table to include an equpment Category and sub catagory to accomodate for diffrerent type of equipment

  ie. Catagory Computer SubCatagory (Desktop, Laptops, Servers)

  hopfully that clears things up. if you have any questions feel free to let me know.

  
  

• MariusVE

  Dean,

  Thanks for all your advice and recommendations on this. I may take this as a springboard for other ideas. My other question was that suppose, after entering all this information for a desktop pc from a user interface point of view, the user clicks on the save button, then I will need to store all this information including warranty information into a main table as most of the smaller tables will have lookup information that will be selectable from a combo. What fields will I need to include in my main table once the information has been saved.

  Regards

  Dipendra

  
  

• DrEvil

  I'm glad I can be of help to you.

  You will find that as you progress with this app you will be coming up with more and more things to add. The one thing to watch out for is making a flat database. I won't get into the theroy of Relational databases here but you can Google( not sure if I'm allowed to say that word in a MSDN Fourm) for Relational Database Design Tutorials.

  Ok with That said Having the User ID in the Main table would be better then Tieing the Asset ID to the User. The reason being is when you have the asset tied to the user and the equipment gets moved to another user then theres 2 edits that have to be made to the Users Table

  1) The existing Users AssetID field value gets emptied
  2) The new users AssetID field gets assign the corosponding ID for the Asset

  Now with making the change in the Asset(Main) Table then you are only making one change The USERID field to corrospond to the USERID of the specific User. also By assigning the USERID to an Asset allows you to have Many Assets to One User (IE. User A has a Printer, Computer, Monitor) This is known as a One to Many relationship and is ideally the type of structure you want to strive for when designing a database.

  as far as your second question. Yes put them in the Asset table. The reason for that is say one of your assets is a computer you want to change the memory. The main assets the same, your just updating it. You could go and make several tables one for HD's one for OS one for Memory and so forth but then your getting into the original problem. Plus, Theres really not a essential reason for tracking assets that closely. Just Having fileds in the Main Assets Table called HDSize, MemSize, OS,CPUSpeed is fine. in the case of an asset not being a computer IE Printer these fields just won't be used for that asset.

  There is just so many possible scenerios and there is no right or wrong way. You just have to look at you basic design and kind of follow it through as you go on you'll come to a point were everything will tie together.

  If you want I could draw up a basic data diagram and send it to you via e-mail

  
  

• LISABET

  Dean,

  Thanks for the info. I will be including a Users table as you mentioned in your last response. I guess I could include the AssetID field into the Users table but in some departments we have users who have for example, a desktop PC and a printer tied to one user. How can I include two assets associated with one user bearing in mind that the UserID field would be unique in the Users table.

  Also, I have an OperatingSystem, HardDriveSize,MonitorSize,MonitorType,CPUSpeed,MemorySize tables which would essentially be lookup tables as they will be selected from a combo from the application. Where can I store this info after saving the data in the application. Will the IDs fo these also go into the main table.

  I really appreciate the great help you are giving me as it enables me to come up with newer possibilities.

  My application will also include PurchaseOrders, ContractManagement, possibly a Helpdesk, Reports as well as an Admin function.

  Regards

  Dipendra

  
  

• James-Jones

  Dean,

  Thanks for your input here in relation to the project I am starting to develop.

  Is it worth storing the printer details into its own main table as the info will be less and the PC/Notebook/Server info into its own table from the examples you have given.

  The HDSize,MemSize,OS,CPUSpeed will just be lookup tables so that it can be selected from the list rather than by user entry and then just the primary ids can be stored in the main table. There would only be two fields in these tables, one being the primary key and the other being the actual name.

  It's all so confusing as I am just trying to get the right approach. I just want to make the user entry in the application as less as possible and select some options from a list.

  If you can send me a basic data diagram this would be a great help.

  My email address is dipendrad@sauflon.co.uk

  Regards

  Dipendra

  
  
• Database Design advice