creating a table w/ 'set' data type column 1

[zpetersen]

Im working on creating an MSDS(material saftey data sheet) index so that my users can access MSDS sheets via web. The front end will have a search function with fields such as "SKU", "UPC", and possibly "MSDS File". The database/table will have fields to match: SKU, UPC, MSDS.

My troubles came when the person requesting the project changed thier order and wanted to be able to store up to 4 UPC #s per SKU. My knowledge of mySQL.. or SQL in general is slim but from what i have read i think that a table utilizing a field called UPC w/ and data type of 'set' might be the answer.

So i guess my question to all of you SQL geniouses is what is my best course of action here. Forgive my vaugness in certain parts of this.. if there is any further information that could help you answer my questions just ask.

Thanks
Zach Petersen
Senior Support Specialist
Cabela's
Prairie du Chien, WI

 

[sleipnir214]

I don't think that's what you want.

First of all, the columntype set is a set of predefined integer values. Not useful for UPCs. Here's more information on the SET columntype:

http://www.mysql.com/doc/en/SET.html

If you need to store a variable number of values with a record, I recommend the use of a related table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[BNPMike]

Don't know what UPCs and SKUs are in this context. If you wish to just store 0-4 UPCs per SKU then one option is to put four fields in the SKU table eg UPC1, UPC2, UPC3 UPC4. For example if you had a table describing an automobile and you wanted to record the mileages of each tyre then you wouldn't really create a new table 'tyre'.

However there are three problems with this approach

1) soon someone disovers you can have 5 UPCs

2) only 1 in a million actually has more than 1

3) you need to store more data about the UPCs as distinct from the SKU - then you hit normalisation problems.

So it depends on the data model. As ever.

 

[zpetersen]

a little info on the date model. basically we have a large warehouse with many different products. each product as a "SKU" (stock keeping unit) to identify it. take a product such as a turkey cooker, it would have a sku of 00000001.

Likewise the product "Turkey Cooker" also has a UPC (Universal Product Code) which is assigned by the manufacturer. on occasion the manufacturer changes the UPC which is where we come to the problem of having one SKU with multiple UPCs.

So im assuming.. with my very rough database knowledge.. that a database might look somthing like this:

SKU_Index - table
+----+-------+---------+---------+
| ID |  SKU  |   UPC   |   MSDS  |
+----+-------+---------+---------+
| a  |   8   |         |filename |
| u  | digit |         |         |
| t  |   #   |         |         |
| o  |       |         |         |
| #  |       |         |         |
+----+-------+---------+---------+
                 ^
                 |
                 |
UPC - table      |
+--------+--------+---------+---------+
|  UPC1  |  UPC2  |  UPC3   |   UPC4  |
+--------+--------+---------+---------+
|   15   |   15   |         |         |
| digit  |  digit |    "    |    "    |
|    #   |    #   |         |         |
+--------+--------+---------+---------+

I appoligize for my bad ascii art...

My plan that i have laid out here includes four 'dynamic' pages. the main "Search" page which will search by SKU, UPC, or MSDS filename. That page will in turn return info to the "Results" page which will list the SKU, all UPCs that SKU, and the msds filename from the MSDS column. the msds filename will be a hyperlink.

The other two pages are admin pages.

(1) Insert
this page will have three fields SKU, UPC, and MSDS filename. when a record is first added it will only have one UPC. This page will need to create a record in the database and fill in the given information into the table(s)(SKU, UPC, and MSDS fields).

(2)Update
When the time comes that an item needs to have the UPC changed or updated this page will be used. The idea is to have a search box that will search the "SKU" field for whatever the user inputs into the "SKU Search" formfield. it will then return ALL information pertaining to that SKU including SKU, all UPCs, and MSDS filename into Text boxes. these text boxes would be SKU, UPC1, UPC2, UPC3, UPC4, and MSDS. the SKU textbox would be locked so that it cannot be changed. the rest of the fields need to be updateable.


Now that I have written a book for everyone to read, heh, most of what i just said might not necessarily pertain to this forum but nevertheless you now have a good idea what I am looking to do. I dont expect anyone to lay this out to the 'T' for me but if somone could point me in the right direction that would be great.

Thanks
Zach Petersen
Senior Support Specialist
Cabela's
Prairie du Chien, WI

 

[sleipnir214]

Actually, your use of the related table is not correct. If you're going to put 4 columns in the related table, you may has well put 4 in the main table.

When doing a related table, the related table will have 2 columns: an ID column which is used to related records to the main table, and a column which stores a single UPC. If a produce has multiple UPCs, it will have multiple related records in the related table.

The reason for doing this is flexibility. Suppose the day comes when you find a product which needs 5 UPCs. If you use the schema you have proposed, then you'll have to modify the table structure. If you store the records "vertically", you only have to add a new record to the related table.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cdlvj]

Just create one table


SKU_- table
+----+-------+--------+---------+---------+
| ID | SKU | eff Dt | UPC | MSDS |
+----+-------+--------+---------+---------+
| a | 8 | | |filename |
| u | digit | | | |
| t | # | | | |
| o | | | | |
| # | | | | |
+----+-------+--------+---------+---------+

key is id,sku, and eff date. No limits, allows for future posting, backward compatible. Effect date should be indexed desc so you can get current record at top.

Today is 1/5/2004 Next month the upc changes, so input 2/15/2004 and dynamically the correct sku is selected.

Regards

 

[sleipnir214]

cdlvj:
How does your schems allow for the storage of multiple UPCs for each inventory item?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[zpetersen]

sleipnir214:
assuming i read what you wrote correctly.. this is how is see it:

-create a database "MSDS"
-in that database create two tables: SKU_Index and UPC_Index
-in the SKU_Index table create columns: ID(autoinc), SKU, and MSDS(to hold the filename)
-in the UPC_Index table create columns: ID(autoinc), and UPC.

Now.. my question is.. do i store all four UPCs (1, 2, 3, and 4) in that single UPC column? and if so which data type would i use.

the next question i would have would be what is the proper way to update/insert records into this database. but i'll burn that bridge later.

once again, many thanks for your help so far.

 

[sleipnir214]

UPC_Index must contain a column which stores the ID value from SKU_Index which that record matches.

You can have either 2 columns (SKU_ID, UPC) or 3 columns (ID(autoinc), SKU_ID, UPC).

If you have an inventory item that has 2 UPCs, then UPC_index will have to rows for that item, one for each UPC.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[zpetersen]

SKU
+---+--------+---------+
|ID |  SKU   |  MSDS   |
+---+--------+---------+
| 1 |00000001|file1.pdf|
| 2 |00000002|file2.pdf|
| 3 |00000003|file3.pdf|
+---+--------+---------+

UPC
+---+-------------+--------+
|ID |     UPC     | SKU.ID |
+---+-------------+--------+
| 1 |000000000001 |    1   |
| 2 |000000000002 |    2   |
| 3 |000000000003 |    3   |
| 4 |000000000004 |    3   |
+---+-------------+--------+

a one to many relationship. i found a book on a coworkers desk.. i think it might help me figure this out a bit more on my own.. i hate asking so many questions. Thanks for your help sleipnir214.

One last question.. if you have time to answer it.. is there a simple way to explain how i get the SKU.ID field to populate with the proper ID from teh SKU table?

 

[sleipnir214]

There are two scenarios.

One is where you are adding a new SKU record and a new UPC record. In that case, your program will add the new SKU record, then use MySQL's last_insert_id() function to retrieve the auto_increment ID generated for the insertion of the SKU record. You can then use that value in the insertion of the UPC record(s).

If you are editing an existing SKU, then you'll know the SKU's id from having fetched it from the database to display it for editing.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cdlvj]

SKU, DATE, UPC, FILEID

00000001,20040101,000000000001,FILE1.PDF
00000002,20040102,000000000002,FILE2.PDF

00000003,20040401,000000000003,FILE3.PDF
00000003,20040101,000000000004,FILE4.PDF
00000003,20030501,000000000005,FILE5.PDF

00000004,20040101,000000000006,FILE6.PDF

The auto id is not necessary, primary unique key is sku and date.

To get sku 00000004 for today 1/5/2004 would retrieve upc 000000000004 and file4.pdf.