How to correctly model an inventory

Frunkie · #1 (**permalink**) 09-04-07, 20:42

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?

r937 · #2 (**permalink**) 09-04-07, 22:44

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

Frunkie · #3 (**permalink**) 09-04-07, 22:56

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.

Frunkie · #4 (**permalink**) 09-05-07, 06:10

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

r937 · #5 (**permalink**) 09-05-07, 06:22

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

Frunkie · #6 (**permalink**) 09-05-07, 15:29

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?

Quote:

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.

r937 · #7 (**permalink**) 09-05-07, 15:35

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

Frunkie · #8 (**permalink**) 09-05-07, 16:54

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?

r937 · #9 (**permalink**) 09-05-07, 17:00

yes, that's what i suggested

Frunkie · #10 (**permalink**) 09-05-07, 17:09

Quote:

Originally Posted by r937

yes, that's what i suggested

I should have listened the first time.

Thanks!

Frunkie · #11 (**permalink**) 09-05-07, 18:09

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?

r937 · #12 (**permalink**) 09-05-07, 18:29

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

Frunkie · #13 (**permalink**) 09-05-07, 19:00

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?

r937 · #14 (**permalink**) 09-05-07, 22:12

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

Frunkie · #15 (**permalink**) 09-06-07, 00:23

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.

Quote:

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?

Quote:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech