Not fix number of rows in a table

Results 1 to 3 of 3

Thread: Not fix number of rows in a table

  1. #1
    Haroon Qureshi Guest

    Default Not fix number of rows in a table

    Hi<BR><BR>I am creating a sales order, I planning to create a four column table with form fields,<BR><BR>1 - Column Product Name<BR>2 - Column Product Rate (Dispaly automatically from <BR> Price table)<BR>3 - Column Quantity <BR>4 - Column Value (Calculat by price * quantity)<BR><BR>Its number of rows are not fix, a user either can enter 1,2 or any number of items in a sales order, also how many form fields should I define... Its tricky for me.<BR><BR>Do anyone have good idea how can I handle this situation<BR><BR>Thanks<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Not fix number of rows in a table

    To maintain a Normalized database, we can have one table as you have mentioned with an ID added to it, and another table which stores UserID(or name or whatever is your key) and the ID from the Product table. <BR><BR>CrossRef table<BR><BR>UserID (some ID of the user)<BR>Product ID (ID of the record entered by the user)<BR><BR>This table can have any number of rows for a particular UserID.<BR><BR>Hope this helps!

  3. #3
    Join Date
    Dec 1969

    Default RE: Not fix number of rows in a table

    Don&#039;t calculate anything in your database... <BR><BR>First of all you need your database to be normalized, meaning, you want to split it up a bit... Here is how I would do it...<BR><BR>Products_table<BR>-------------------------<BR>Product_id (Autonumber or part number or whatever)<BR>Product_Name (name of the product)<BR>Product_Description (Description of the product)<BR>Price (the price of 1 product)<BR><BR>Customers_table (table of people who order products)<BR>Customer_ID (autonumber)<BR>Customer_Name (duh ;) )<BR>** Any other customer information would go here **<BR><BR>Orders_table -This table is just a holder for order items<BR>-------------------------<BR>Order_ID (autonumber)<BR>Customer_ID (Foreign key to the customers table)<BR><BR>Order_Items<BR>-------------------------<BR>Item_ID (Autonumber)<BR>Order_ID (Foreign key to the orders table)<BR>Product_id (Foreign key to products table)<BR>Quantity (Number of products ordered)<BR>Sell_Price (Price of the product when it was purchased)<BR><BR>Then, when a customer is filling out an order, they can have 1 order with many products on that order (Each different product they order will have 1 entry in the Order_Items table) Using foreign keys will allow you to have your data be rather portable, meaning if/when you change the price or name of a product, it will change across the board, and you can keep better track of your orders this way. Removing and adding parts to order will be made easier as well...<BR><BR>Notice on the Order_Items table there is a "sell_price" field. Products may change in price from time to time, the sell price field in this table will allow your records to remain intact so that if your prices go up or down, you will still have record of how much your customer was actually billed for and actually paid for...<BR><BR>Hope this helps..<BR><BR>-- Whol

Posting Permissions

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