in reply to Managing Inventory Sections with Perl and SQL

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
)

[download]
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
)

[download]
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()

[download]
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

[download]

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

Michael

Replies are listed 'Best First'.
Re: Re: Managing Inventory Sections with Perl and SQL
by Anonymous Monk on Jul 29, 2003 at 07:12 UTC

    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! :)

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

      Michael

[reply]

        I double checked that and it appears correct. I somehow got the idea from the docs that the decimal place took up a spot (maybe I confused it with the sign). But you're right, thanks :)

[reply]
Re: Re: Managing Inventory Sections with Perl and SQL
by Anonymous Monk on Jul 30, 2003 at 01:03 UTC

    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),
);

    [download]

    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! :)

[reply]
[d/l]
[select]
      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

[reply]

In Section Seekers of Perl Wisdom