• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

RDBS Data Modeling

Is there anywhere that I can get help modelling my system into an efficient database model?  I have tried a number of times to design out the tables and relationships but keep getting mixed up and running into problems.  I shall post the outline here and any help or advice would be very much appreciated.

I have a stock room with items of stock.
These items of stock are used to create sets of product which are then shipped.
Given the length of the final product, I can make a judgement on the amount of each product a set that will be shipped, however this is not fixed.
Stock items can also be sold individually.

I receive an order from the sales department detailing the qty and lengths of sets to send.  These orders can also, as stated before, be for individual items. ie not in sets / spares

I would like to be able to add sets to the database and edit the stock items and quantities associated with each set as I go.

Any ideas?


1 Solution
A good database design is expected not only to perform efficiently, but to support certain business requirements.The first step in is to define and scope those business requirements. Your brief 5-line explanation, suggests to a largish scope and I would not be surprised if you approached 100 tables.  That's too much to bite off in this forum, so I'll focus on what I think may be one of the trickier parts.

I like to begin with a logical model that expresses clear, concise definitions and some of the business rules.  Of course, I don't know your business, but these should give you an idea of how to proceed:

Party - a person or business of interest to Acme Corporation.  Party is a high-level entity that represents the common attributes of Customers, Vendors, Employees and other PartyTypes.

Customer - a Party that has purchased or has intent to purchase a Product from Acme Corporation. A Customer must have exactly one a credit card on file.  If the card is expired, only cash purchases are permitted.

PartyType -  the type of role between Acme Corp and a Party.  A Party must participate in at least one role, but may participate in many.  Examples of PartyType are Customer, Vendor, Reference, Contractor, and Employee

StockItem - A component may be sold as a Product or that is used to build a Product.  For example, a toothbrush may be sold as is or a pair of toothbrushes may be boxed with toothpaste and floss to make a dental kit.  StockItems are kept in StockRooms both before and after assembly.

Product - An individual stockitem or an assembly of stock items that is for sale to a Customer. Any StockItem may be sold and is, therefore, also a product.  

Category - a classification of Products used for organizing them into groups familiar to Customers

ProductCategory - a Category to which a Product belongs.  A Product may be unclassified or may belong to one or more Categories.

StockRoom - A storage place for StockItems and Products.  
Invoice - an instrument for recording the sale of one or more Products to a Customer. Since Customer Accounts are not supported, each Invoice represents the de-fact accounting record for a sale,  One Customer must have responsibility for an Invoice/Sale.  

Naturally, your actual requirements will be different than the ones I made up and will  produce a different database design.  But, based on the above, my design begins to look something like this:

  PartyID (PK)

   PartyID (PK) (FK-->References Party.PartyID)
   CustomerNo (UK)


   PartyID (PK) (FK references Party.PartyID)
   PartyType (PK) (FK references PartyType.PartyType)
StockItem  (note: consolidated with Product)
  StockItemID (PK)
  UPC Code
  UnitOfMeasure (FK references UnitOfMeasure.UnitOfCode)
  AssemblyID (PK) (FK references StockItem.StockItemID)    
  StockItemID (PK) (FK references StockItem.StockItemID)

   UOM_Code (PK)


    StockItemID (PK) (FK references StockItem.StockItemID)
    CategoryCode (PK) (FK references ProductCategory.CategoryCode)

    StockRoomCd (PK)

    StockItemID (PK) (FK references StockItem.StockItemID)
    StockRoomCd (PK) (FK references StockRoom.StockRoomCd)
viralssAuthor Commented:
That's absolutely awesome, thanks a million for taking the time to write such a detailed response.  Very very much appreciated!


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now