Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    How to correctly model an inventory

    I have four tables for inventory. Here are two of my tables.

    Code:
    inventory
    inventory_Type
    acquisition_Date
    quantity
    mfgr
    model
    inventory_Id (PK)
    
    (unique constraints are set on mfgr, model)
    
    inventory_Serial
    inventory_Id (PFK)
    serial_Number (PK)
    Here are 2 records from these tables:
    Code:
    Radio	2007-09-04	100	Nextel	K634		1
    I am wondering if it is correct to put the quantity inside of the inventory table. For example, if I had 100 nextel radios, should I enter that radio 100x? or just once and then tie the PK of the inventory table to a serial number table where I would have 100 unique serial numbers?
    Last edited by Frunkie; 09-04-07 at 22:43.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i would use "product" instead of "inventory" as the name

    is it important that you track each serial number?

    by way of comparison, if you buy a box of cereal, that box has a 13-digit EAN barcode number, but individual boxes are not distinguishable, so it makes sense to talk about 100 boxes of mini-wheats

    it all comes back to the whole purpose of a PK: to confer identity

    how many products have the same serial number?

    and let us have no talk about a "serial number table" because it is not the numbers you are keeping track of, but the products that carry those numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Thanks Rudy.

    It is not important to track the serial number as much as it is to attach that number to a specific device (Nextel radio etc.). I put the serial number into its own table to allow items that are not serialized such as a desk or whatever.

    What I wanted to do was to be able to identify who had exactly what piece of equipment but it is not working that way and I clearly see why that is. (By removing the serial number, I have removed the identity from the product)

    To track the non serialized stuff, would you recommend that I place the serial number back into the inventory (will be renamed products) table and make it nullable?

    EDIT:
    I have decided to only track the serialized items and not really worry about the little, non serializes items.
    Last edited by Frunkie; 09-05-07 at 01:06.

  4. #4
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    I am wondering if there is a way to model these products as they are loaned out and returned. It would be like a piece of equipment that the company owns and gives to an employee for a shift; the employee then returns that equipment at the end of their shift and it would then be loaned out to a different employee. I would also like to keep the history on these loans.

    I cannot model this correctly. If someone can get me started, I would appreciate it.

    I believe that I should start with three tables. The first one for products in and the other for products out and then a products table to hold all of the actual products.

    Thanks..

    Frank

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try two tables: one for products, and one for productsloaned

    the PK of products will be product_id (notice how this does not say whether it's a surrogate or natural key, just that there is a column which can be used as the PK)

    the PK of productsloaned will be {product_id,dateloaned} -- so a product can be loaned out only once on a single day

    who it's loaned to is a non-key attribute, and when it's returned is also a non-key attribute

    there, that should get you started

    is this table structure sufficient?

    not by itself, no, because you want to avoid things like loaning out a product that hasn't been returned yet

    the best way to accomplish these additional checks is with something called a CHECK constraint, but mysql doesn't support them, so you'll have to do it with application code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    try two tables: one for products, and one for productsloaned

    the PK of products will be product_id (notice how this does not say whether it's a surrogate or natural key, just that there is a column which can be used as the PK)

    the PK of productsloaned will be {product_id,dateloaned} -- so a product can be loaned out only once on a single day

    who it's loaned to is a non-key attribute, and when it's returned is also a non-key attribute

    there, that should get you started
    well, I guess I was on the right track because that was what I originally modelled, then I noticed that there was no ingrety because I could issue the same product out to another employee without first having received it back.

    What I thought I would do would be to break out the date_in non key attribute from the productsloaned table and put it into another table called products_In and include the PK from the products table.

    I would use the PK from this new table as a FK in the productsloaned table to ensure that a product could not be loaned out without having been returned first. Is this correct?


    is this table structure sufficient?

    not by itself, no, because you want to avoid things like loaning out a product that hasn't been returned yet
    I noticed this when I used that design and concluded that it was not correct. It was because of this that I put the date_In into its own table and made its PK a part of the PK in the products_Loaned table.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    What I thought I would do would be to break out the date_in non key attribute from the productsloaned table and put it into another table called products_In and include the PK from the products table.

    I would use the PK from this new table as a FK in the productsloaned table to ensure that a product could not be loaned out without having been returned first. Is this correct?
    is that correct? i'm sorry, i cannot tell just from that cursory overview

    why don't you test it and see?

    that is, create the tables, and start adding rows

    when you have added sufficient rows that you think properly illustrate all scenarios (an item never loaned out, an item loaned out but not returned, and item loaned out, returned, and loaned out again, etc.), then show us the data

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Ok, 3 tables total.

    Here are 2 rows of data from the product table. product_Id #1 is being loaned out to employee #1. product_Id #2 is not loaned out at all.

    here is the DDL for this table:

    Code:
    CREATE TABLE `product` (
      `mfgr` char(20) NOT NULL,
      `model` char(20) NOT NULL,
      `product_Id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`product_Id`),
      UNIQUE KEY `product_Id` (`product_Id`),
      UNIQUE KEY `Alter_Key1` (`mfgr`,`model`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Here is some sample data from the table:
    Code:
    Erikson		Z321	2
    Motorola	X123	1
    I don't see anything wrong with the product table.
    -----------

    DDL for the product_In table:

    Code:
    CREATE TABLE `product_In` (
      `product_Id` int(11) NOT NULL,
      `received_From` int(11) NOT NULL,
      `date_In` date NOT NULL,
      `in_Id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`in_Id`),
      UNIQUE KEY `in_Id` (`in_Id`),
      UNIQUE KEY `Alter_Key6` (`product_Id`,`date_In`),
      CONSTRAINT `product_in_ibfk_1` FOREIGN KEY (`product_Id`) REFERENCES `product` (`product_Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Here are 2 rows of data from the product_In table:
    Code:
    1	1	2007-09-05	1
    1	1	2007-09-06	2
    While entering the same product_Id number and date_In fails, I have noticed that by changing the date I can now enter that product with an "In" status. Not good.

    DDL for the product_Out:

    Code:
    CREATE TABLE `product_Out` (
      `in_Id` int(11) NOT NULL,
      `issued_To` int(11) NOT NULL,
      `date_Out` date NOT NULL,
      `out_Id` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`out_Id`),
      UNIQUE KEY `out_Id` (`out_Id`),
      UNIQUE KEY `Alter_Key7` (`in_Id`,`date_Out`),
      CONSTRAINT `product_out_ibfk_1` FOREIGN KEY (`in_Id`) REFERENCES `product_in` (`in_Id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Here are 2 rows of data from that table:

    Code:
    1	4	2007-09-05	1
    1	4	2007-09-06	2
    Just like the product_In table, changing the date allows me to enter another row od data. Not good.

    I am thinking that by going back to (2) tables, product and product_Loaned and adding two non key attributes for dates in and out I would be able to enforce the ingrety through applicaton code. Correct?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, that's what i suggested
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    yes, that's what i suggested
    I should have listened the first time. Thanks!

  11. #11
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Rudy, if I may ask this question for clairification.

    My reasoning behind removing the date_In attribute from the product_Loan table and putting it into a loan_In table was to enforce the integrety for a product on loan that could not be loaned out a second time.

    You told me to have 2 tables and to keep the date_In and date_Out attributes in the product_Loan table. Is this because of identity?

    I think that by removing the date_In attribute I have effectively removed 1/2 of the ability to fully identify the product.

    Am I correct?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it's about identity

    next question: identity of what?

    it's not a products table, it's a products loaned table

    when an item goes out, you timestamp the "out" column and set "in" to null

    if it's still out, that's one row

    if it comes back, update "in", and it's still one row

    that's one loan

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    yes, it's about identity

    next question: identity of what?
    The identity would be for the product on loan, or product_Id in this case.

    As far as constraints go, I would have a unique constraint on product_Id and the timestamp column. By using a timestamp instead of a date, I would not limit myself to a single loan per day. correct?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    The identity would be for the product on loan, or product_Id in this case.
    not quite, but you are almost there

    identity == PK

    if it were for product_Id, then a given item could only be in the table once, in effect you would be saying it can only ever be loaned once

    Quote Originally Posted by fjm1967
    As far as constraints go, I would have a unique constraint on product_Id and the timestamp column.
    you are getting really warm, now

    how about this: if "the timestamp" you are talking about is the "out" time, then instead of a unique constraint, make product_ID together with the "out" time the primary key

    Quote Originally Posted by fjm1967
    By using a timestamp instead of a date, I would not limit myself to a single loan per day. correct?
    that is correct

    in effect you are saying that you can loan out a book at eleventeen milliseconds after 9:37 pm and then loan it out again at twelvety milliseconds seconds after, assuming, of course, that your application logic has confirmed that the item was actually brought back (so that the "in" timestamp was updated) in between those times
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by r937
    not quite, but you are almost there

    identity == PK

    if it were for product_Id, then a given item could only be in the table once, in effect you would be saying it can only ever be loaned once
    Ok, I figured this out almost immediately after my last post because I couldn't enter more than 1 row. I corrected it as you can see in the DDL below.

    make product_ID together with the "out" time the primary key
    Ok, here is my DDL, Rudy. Would you please look at this because I have never understood when you say to make something "part of the primary key".

    Code:
    -- Table "product_Loan" DDL
    
    CREATE TABLE `product_Loan` (
      `product_Seq` int(11) NOT NULL,
      `issued_To` int(11) NOT NULL,
      `loan_Out` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
      `loan_In` date default NULL,
      `issued_By` int(11) NOT NULL,
      `loan_Out_Seq` int(11) NOT NULL auto_increment,
      PRIMARY KEY  (`loan_Out_Seq`),
      UNIQUE KEY `product_Seq` (`product_Seq`,`loan_Out`),
      UNIQUE KEY `loan_Out_Seq` (`loan_Out_Seq`),
      KEY `issued_By` (`issued_By`),
      KEY `issued_To` (`issued_To`),
      KEY `loan_Type` (`loan_Type`),
      CONSTRAINT `product_loan_ibfk_1` FOREIGN KEY (`issued_By`) REFERENCES `employee` (`employee_Number_Seq`),
      CONSTRAINT `product_loan_ibfk_2` FOREIGN KEY (`issued_To`) REFERENCES `employee` (`employee_Number_Seq`),
      CONSTRAINT `product_loan_ibfk_3` FOREIGN KEY (`product_Seq`) REFERENCES `product` (`product_Seq`),
      CONSTRAINT `product_loan_ibfk_4` FOREIGN KEY (`loan_Type`) REFERENCES `product_loan_type` (`loan_Type`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    Here is how I am reading this.. I have 1 PK and that is loan_Out_Seq. That is an IDENTITY column. Now, in mysql, for some reason, whenever I use an IDENTITY column, I am not able to add anything else to the PK. I get an error that says there can only be one auto-incrementing column; even though the other column I want to add is not set to auto-increment and is of the same datatype.

    I can however, create a unique index, as in my example, would be product_Seq and loan_Out (Timestamp). Is this the same thing as adding a column to the PK?

    One other oddity that I noticed was when I tried to add a second timestamp for the date_In. Again, mysql complains and says there can only be 1 timestamp column in the table. Am I doing something wrong? All of my tables are set to use the Innodb engine. Does this make a difference by chance?

    in effect you are saying that you can loan out a book at eleventeen milliseconds after 9:37 pm and then loan it out again at twelvety milliseconds seconds after, assuming, of course, that your application logic has confirmed that the item was actually brought back (so that the "in" timestamp was updated) in between those times
    Yes, excactly.
    Last edited by Frunkie; 09-06-07 at 01:27.

Posting Permissions

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