RDBS Data Modeling

Hi
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?

Thanks

Shane
viralssAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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:


Party
  PartyID (PK)
  PartyName
  PartyAddress
  etc

Customer
   PartyID (PK) (FK-->References Party.PartyID)
   CustomerNo (UK)
   Status
   ShippingAddress
   StartDate
   CreditCardNumber
   CreditCardType
   CreditCardCVC
   CreditCardExpiration
   etc

PartyType
   PartyType(PK)

PartyRole
   PartyID (PK) (FK references Party.PartyID)
   PartyType (PK) (FK references PartyType.PartyType)
   Status
   
StockItem  (note: consolidated with Product)
  StockItemID (PK)
  Description
  UPC Code
  UnitPrice
  UnitOfMeasure (FK references UnitOfMeasure.UnitOfCode)
 
StockItemAssembly
  AssemblyID (PK) (FK references StockItem.StockItemID)    
  StockItemID (PK) (FK references StockItem.StockItemID)
  StockItemQuantity

UnitOfMeasure
   UOM_Code (PK)

ProductCategory
   CategoryCode(PK)
   Description

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

StockRoom
    StockRoomCd (PK)
    Description

StockItemLocation
    StockItemID (PK) (FK references StockItem.StockItemID)
    StockRoomCd (PK) (FK references StockRoom.StockRoomCd)
    OnHandQuantity
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
viralssAuthor Commented:
That's absolutely awesome, thanks a million for taking the time to write such a detailed response.  Very very much appreciated!

:)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.