Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Plumbing database design

    I am new to creating databases. I am in a small department and was given the task of inventory control. I am trying to design a database for the supplies we have. We have a rather extensive inventory. It has alot of parts with same size but different material, same materials different size, and so on. What is the best way to make an inventory database for plumbing parts? Should I:

    Parts Table:
    Type--(i.e, elbows, tee, ...)
    Material--(i.e, PVC, Brass, Galvanized)
    Size--(i,e, 1", 2", 4"...)
    Cost--
    Quantity--

    or

    Type Table:
    Elbows
    Tee
    Material Table:
    PVC
    Brass
    Galvanized
    Size Table:
    2"
    4"

    and so on.
    I eventually want to tie it in with invoicing. Thanks in advanced for any help.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    The "correct" design is the one that serves your needs. The "most correct" design is the one that serves your needs the best.

    The design that you've started with seems fine to me. Does it address your needs?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2014
    Posts
    2
    I'm not sure, because I have so many parts with different sizes. There are so many combinations that 1 table would have so many fields to cover the inventory. I guess my questions is should I make each individual piece its own
    record. Such as 1" pvc elbow $.85, 3/4" galvanized coupling $1.05. or make different tables for size, piece, material and then cross reference to save from redundancy of size, type, and material. I'm trying to make it versatile as I can. Thanks for the quick reply.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Like most things in database design, it depends.

    If you think of the size as a "thing" separate from pipe, fitting, elbow, etc. then you can (and probably should) use a table of sizes. This is probably a good idea, since you'll probably want to build your application to make it easy to deal with "half inch widgets" without regard to what the widget actually is.

    The flip side of the observation is when you think of size as a measurement. If you deal with a lot of custom work (like a specialized machine shop or casting house), then measuring the diameter might make sense... Knowing that a pipe is 72.028 centimeters long is important for the purpose of inventory. Measuring the inside diameter isn't common for stock parts, since sizes tend to fall into a small number of rigidly defined sizes. If you are creating stock, then treating the size as a measurement makes sense.

    In the case of stock, then it makes sense to treat size as a code that could be an INT datatype with its own table to keep track of facts related to each size. In the case of custom built parts, then size is a measurement and probably ought to be a REAL or a DECIMAL datatype.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •