SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Don't delete the product out of your product table instead add a boolean (yes/no, single digit number, or whatever it is called in your databas) that specifies whether the product is currently available or not. Then you can check this field to show in your catalog or not and the product will still show in your order details. Chances are your product table won't grow as fast as your order details table and keeping the product in the table also makes it easier to offer the same product again in the future if desired or to process returns and refunds.

    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

    [This message has been edited by wluke (edited June 27, 2000).]

  2. #2
    SitePoint Member
    Join Date
    Jun 2000
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I am currently developing a database that is for my own e-commerce site. Before I go ahead and explain my problem I just want to say I'm a beginner.

    My database consists of several tables:
    Product Table - Stores product information
    Account Table - Stores information about a user
    Cart Table - Stores information about what the user has put in their virtual shopping cart.

    What I would like to do is create a proper Order Table and an Order Details Table which holds information about past user purchases. To reduce redundancy I've created a field that links a product ID from the Product Table to the Order Details Table. The problem with this is when a product is deleted for some reason, then all the product IDs in the Order Detail Table that are associated with that particular product are pointing to nothing. What should I do to prevent this delete anomaly?

    There is the option to leave all the products in the Product Table even if a product is discontinued. But this just increases the size of the table. I would also probably have to add some field to indicate that the product should not be looked up when a user on my site looks for a particular product.

    The other idea might be to just copy the data from the Product table to the Order Details Table. That way if the product is deleted in the Product Table it wouldn't affect the Order Details Table. But the problem with this is redundancy, inconsistent data (i.e. spelling mistakes) and a table that can quickly increase in size.

    It would be a great help if someone could provide me with any kind of information about how to go about this problem.

    Thanks in advance

    Michael C.


  3. #3
    SitePoint Member
    Join Date
    Jun 2000
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would it be a good idea to index the "Discontinued" field in the Product Table? That way when I create a SELECT query statement, the database will retrieve those products that aren't discontinued faster. Or would it be better to create a primary key in the Product table as the Product ID and the "Discontinued" field tied together for faster table queries?

    Mike C.

  4. #4
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What DBMS are you using?

    If your using some sort of SQL database on a fast server I would use direct queries i.e.
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    select * from products where category='XXXXX' and discontinued = 0
    [/code]

    This would return all the products in that category that your still selling. If you get more than a thousand categories then yeah you want to index it. If your using MS-Access then you want to index it from the start.

    If your using a system like SQL Server, Oracle or Sybase then I would store the queries as procedures and have those procedures return recordsets.
    ie.
    <BLOCKQUOTE><font size="1" face="Verdana, Arial">code/font><HR><pre>
    sqlresult = dbconn.exec getproducts(1)
    [/code]

    This won't work in MySQL though, because they don't allow stored procedures.

    You could do the same thing in Access but your "procedure" will be in a VBA module.


    ------------------
    Wayne Luke - Sitepoint Moderator/Internet Media Developer
    Digital Magician Studios - Making Magic with Web Applications
    wluke@digitalmagician.com

  5. #5
    SitePoint Member
    Join Date
    Jun 2000
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not too sure how many products will be in the Product Table down the road, but I'm pretty sure it will be a couple hundred. So indexing the Discontinued field seems like the best approach. The database I'm currently using is MS Access. If my database gets used heavily or the size of it increases rapidly, not because of redundancy issues, I always have the option to just migrate the information to MS SQL Server.

    Oh, you mentioned using a VBA module as a type of stored procedure in Access which is something I have been wondering about. By making a module in Access how can you execute it via an ASP page? Is there any web site out there that provide documentation and examples about this?

  6. #6
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Checkout Parameterized Procedures in MS-Access, Various methods to call Stored Procedures, and The Stored Procedure. You can also check out the MSDN library on Microsoft's site.

    [This message has been edited by wluke (edited June 28, 2000).]


Bookmarks

Posting Permissions

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