Managing Inventory Sections with Perl and SQL

[Anonymous Monk]

I'm throwing together an inventory database with a Perl front-end. The items in the database will have descriptions like name, photo-location, price, etc. They will also be assigned to a section.

My question is - using Perl, what would be the most effective ways to populate a web page with items from a certain section? Storing the section as a column of the item row seems inefficient because I'd have to check each row to see if it has a certain section (is this a real efficiency problem?). Would something that links item id's to sections be much better?

Also, as for having a price field - If I want to do something like cross out the old price and enter a sale price, what's the best way to do this? Should I have a price and sale_price field and if the sale_price field isn't NULL, then insert the HTML as a template variable?

Any insights on these, or related questions would be greatly appreciated. Thanks in advance!

[mpeppler]

As you mention SQL I'll assume that you are using some form of relational database system (MySQL, MS-SQL, or something similar).

The first thing to consider is the design of your database tables. Off hand I'd start with something like this:

create table section (
   sectionId    int
,  sectionName  varchar(32)
,  dateCreated  date
-- maybe some other fields that describe the section
-- status
)

create table inventoryItem (
   itemId            int
,  itemName          varchar(32)
,  sectionId         int
,  description       varchar(255)
,  photoLocation     varchar(255)
,  dateCreated       date
,  dateLastModified  date
,  status            int
,  price             numeric
,  salePrice         numeric NULL
)

Note: I'm using sort-of generic datatype names, you'll have to adapt for your particular database.

If you want maximum flexibility for the pricing of your items you'd break out the price from the inventoryItem table and create a table just for the price:

create table itemPrice (
    itemId      int
,   price       numeric
,   type        int         -- 0 for normal, 1 for sale
,   validFrom   date        NULL
,   validTo     date        NULL
)

By using this price table you can store a default price for the item (with type = 0 and validFrom/validTo left NULL), and any sale price entered with the type == 1 and appropriate dates for validFrom and validTo.

Now to fetch the items for a section you'd have a query that would look somewhat like this:

select s.sectionName
     , i.itemId
     , i.itemName
     -- other inventoryItem columns as needed
     , p1.price
     , "sale_price" = p2.price
  from section s left join inventoryItem i 
        on s.sectionId = i.sectionId 
    left join itemPrice p1 
        on i.itemId = p1.itemId and p1.type = 0 
    left outer join itemPrice p2 
        on p1.itemId = p2.itemId and p2.type = 1 
       and p2.fromDate < getdate() and p2.toDate > getdate()

where getdate() returns the current date.

This will return the appropriate rows, like this:

            itemId             price                sale_price
 ---------- ------- ---------- -------------------- ------------------
+--
 section 1        1 item 1                24.990000                 NU
+LL
 section 1        2 item 2                19.990000            14.9900
+00

Once you have the data fetched it becomes relatively easy to format it for your web page.

Michael

[Anonymous Monk]

Nice solution! I like the flexibility. Thanks a lot :)

One more question: I'm using MySQL and wondering what format to store the price in, I was thinking an unsigned decimal(9, 2) would this be appropriate (If I remember properly it should allow values up to 999 999.99, correct?).

Thanks again! :)

[mpeppler]

decimal(9,2) means store up to 9 digits, with 2 digits precision, so you can store up to 9 999 999.99.

Michael

[Anonymous Monk]

Hi, here's what I've got so far:

create table section (
    section_id   int(9) unsigned not null,
    section_name varchar(32),
    created      datetime default '0000-00-00 00:00:00' not null,
    primary key (section_id)
);    

create table inventory_items (
    item_id     int(9) unsigned not null auto_increment,
    section_id  int(9) unsigned,
    item_name   varchar(32) not null,
    description varchar(255),
    photo_name  varchar(255),
    created     datetime default '0000-00-00 00:00:00' not null,
    modified    datetime default '0000-00-00 00:00:00' not null,
    primary key (item_id),
);

create table item_price (
    item_id    int(9) unsigned not null auto_increment,
    price      decimal(11, 2),
    sale_price decimal(11, 2),
    valid_from datetime not null,
    valid_to   datetime not null,
    primary key (item_id),
);

I'm currently receiving an error "ERROR 1064 at line 8: You have an error in your SQL syntax near ')' at line 10." I'm running this from a file like so: mysql inventory < inventory.sql -u username -p I was receiving an error that said the same but at line 6 before, I removed the comma after primary key and it seemed to change the error - could this have something to do with whitespace/comma placement? I noticed in your example you placed the preceeding line's comma before the next statement.

Thanks! :)

[mpeppler]

The placements of the commas in my example are just habit - that should not be an issue.

However I don't really know MySQL, so off-hand I don't see where the syntax error could be. Personally I'd split this out to create each table separately, and that might make it a little easier to find the problem.

In addition I think that you have a slight logic or design problem: Your "item_price" table has primary key "item_id" - which means that you can only have one row in that table for any item, which also means that you can't keep a price history - maybe you don't need it at this point, but I thought I'd point it out.

Michael

[daeve]

A node that may be of interest is: My Experiences with using CGI::Application and Template::Toolkit To Build an Online Shopping Cart

HTH
Daeve

[Anonymous Monk]

You'll probably also enjoy reading How to Avoid Writing Code by Kake Pugh, author of CGI::Wiki

[Ionizor]

In terms of efficiency, the best advice I can give is to construct your SQL statements carefully. Retrieving all the rows and then filtering for a certain section using Perl is very inefficient. Let the database worry about section for you by only SELECTing where section is the one you're looking for.

It sounds like you could use a crash course in database design - it's too complicated to get into here. A quick Google search turned up a W3Schools SQL course but it doesn't really get into the design aspects of databases. I also turned up a howto article on Lycos' "webmonkey" site which looks much more promising.

Best of luck.

--
Grant me the wisdom to shut my mouth when I don't know what I'm talking about.

[CountZero]

Most important when doing your reading on relational databases is learning how to normalize your database tables. Having a good normalized database gives you improved performance, a robust design and is also easy to maintain.

Have a look at Basics, First Normal Form, Second Normal Form, Third Normal Form and Fourth Normal Form.

This is also a good place to start: it has a lot of further links.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[aquarium]

looks like you need to read up on relational databases to get the full benefit, BUT here goes: do store the section name in a separate field, indexing on that field if necessary....later on when you do the sql query you do SELECT field_name1, field_name2, etc FROM table_name WHERE section_field = $your_desired_value
on the issue of prices: usually store RRP and SOLD prices, as well as $ taxed and even conversion rate and currency name if used to sell to overseas customers.

[The Mad Hatter]

As far as I know, checking each row for the value of a column shouldn't be too intensive to matter. If that isn't what you want to do, then the only other option (that is apparent to me) is to have a table for each section, but that can get ugly easily.

[schweini]

hmm...i'm doing this rather big inventory-thingy myself, and i - even though i completly lack any formal SQL-education must warn against being TOO formalistic. i was striving towards complete orthogonality (sp?) and used indices everywhere - but now i have SQL statements that basically only consist of tons and tons of JOINs, which can get really, really slow if you have a moderate database (>100k rows). and even though i fear that i'll loose my recently aquiered status of 'monk' by suggesting this, i'd say that if you have information (like sections) that don't change too much, and wont clash too often, at least give the over-simplistic "simply-put-the-name-in-the-column" approach at least a thought....for simple systems, i'd almost say that it's the better approach. (the people that did the old system which i am replacing always wonder why i have to use all that SQL for some simplistic stuff, when they simply ran through the tables without really thinking too much...but then again, i am replacing their system ;-)
that being said, here's what i am doing:
every item has a couple of columns that are 'group-ids'. in a seperate table i store all grouping-information (id, name, subgroup of, contains items of type id X, etc.). i might migrate the 'id' part to actually being a 'id-path', i.e. "000100060007" would mean 'item is in subgroup 0007 of subgroup 0006 of group 0001' - that way, listing all items in group 0001 and below is trivial (in contrast to eternal 'does this group have any sub-group?' loops).
i manage the prices with a field containing the percents of discount (but that was a requirement). your approach looks fine to me.
good luck, and keep it simple - some times the simpler way IS the better way, IMHO (my theory being that so many geeks have long beards because they never heard of occham's razor ;-). inventory systems are hell, i tell ya!

but please DO take all this with a load of salt - i'm a radical autodidact.

[barrd]

Hi Anonymous Monk,
If you're attempting this as an exercise for yourself to learn about Databases and/or Perl in general then the links above provide some great material. However, if this is something you need in a hurry then I can highly recommend Interchange - part of the RedHat eCommerce suite. It does everything you have asked about and much, much more. There is a large community behind it, extensive documentation and feature rich management "backend". Its written in Perl and allows embedded Perl in its pages (or ASP, PHP like syntax if that tickles your fancy ;).

It's freely available for download (under the same style of licence as Perl itself)... go ahead, take a look.

Best of luck to you.

[dga]

I would add 2 things to what has been said.

An index on the section field should make lookups of section efficient.

When I have done similar things, I have had prices in a seperate table with an effective date so that prices could be changed by a date and historic price info could be gathered.

So for your sale example, say that from August 1-8 you were having a sale then you would have 2 entries in the price table for your item one with the sale price and August 1 and one with the normal price for August 8th. A sale boolean could also be put in this table to indicate sale prices if desired etc.