This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Oct 16, 2012 8:21 AM by 967000 RSS

Is there a datatype that allows me to store more than one item at a time

967000 Newbie
Currently Being Moderated
Hello Everyone,

Is there a datatype that allows me to store more than one item at a time , in a column in a row?

I have to prepare a monthly account purchase system. Basically in this system a customer purchases items in an entire month as and when required on credit and then pays at the end of the month to clear the dues. So, i need to search the item from the inventory and then add it to the customer. So that when i want to see all the items purchased by a customer in the current month i get to see them. Later i calculate the bill and then ask him to pay and flushout old items which customer has purchased.
I am having great difficulty in preparing the database.
Please can anyone guide me! i have to finish this project in a weeks time.

Item Database:
SQL> desc items;
Name Null? Type
----------------------------------------- -------- ----------------------------
ITEMID VARCHAR2(10)
ITEMCODE VARCHAR2(10)
ITEMPRICE NUMBER(10)
ITEMQUAN NUMBER(10)

Customer Database:
SQL> desc customerdb;
Name Null? Type
----------------------------------------- -------- ---------------------
CUSTID VARCHAR2(10)
CUSTFNAME VARCHAR2(20)
CUSTLNAME VARCHAR2(20)
CUSTMOBNO NUMBER(10)
CUSTADD VARCHAR2(20)

I need to store for every customer the items he has purchased in a month. But if i add a items purchased by a customer to the customer table entries look this.
SQL> select * from customerdb;

CUSTID CUSTFNAME CUSTLNAME CUSTMOBNO CUSTADD ITEM ITEMPRICE ITEMQUANTITY
---------- -------------------- -------------------- --------- --------------------------- ---------------- --------- -------------------- ----------------------
123 abc xyz 9988556677 a1/8,hill dales soap 10 1
123 abc xyz 9988556677 " toothbrush 18 1

I can create a itempurchase table similar to above table without columns custfname,csutlnamecustmobno,custadd

ItemPurchaseTable :

CUSTID ITEM ITEMPRICE ITEMQUANTITY
---------- --------- -------------------- ----------------------
123 soap 10 1
123 toothbrush 18 1

ill just have it as follows. But still the CUSTID FK from CustomerDB repeats for every row. I dont know how to solve this issue. Please can anyone help me.

I need to map 1 customer to the many items he has purchased in a month.

Edited by: Yukta Lolap on Oct 8, 2012 10:58 PM

Edited by: Yukta Lolap on Oct 8, 2012 11:00 PM
  • 1. Re: Is there a datatype that allows me to store more than one item at a time
    jeneesh Guru
    Currently Being Moderated
    Welcome to the forum..
    Yukta Lolap wrote:
    I can create a itempurchase table similar to above table without columns custfname,csutlnamecustmobno,custadd
    You are on the right track..
    ill just have it as follows. But still the CUSTID FK from CustomerDB repeats for every row.
    What is the problem in that? That is how RDBMS works..

    Other option is nested table.. But never go for that - They are difficult to maintain and difficult to even query..
  • 2. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    But doesnt the repetition of the CUSTID for every item he purchases waste that column memory? I mean thats like redundancy right?
  • 3. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    I am really new so please can you confirm it form me? Like i am doing the right thing so i can proceed with the project. I have been at a standstill because of this.
  • 4. Re: Is there a datatype that allows me to store more than one item at a time
    jeneesh Guru
    Currently Being Moderated
    Yukta Lolap wrote:
    But doesnt the repetition of the CUSTID for every item he purchases waste that column memory? I mean thats like redundancy right?
    How it is reduntand?

    Reduntancy is when you are storing without a reason, like storing cust_name in purchase table..

    Here without storing cust_id, how can you achieve your objective?

    It is not reduntant, but relational..
  • 5. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    Thank you so much Sir!! Your answer has really solved my issue. So i dont need to change the database design right. This is ok to move ahead to design a simple stand alone applicationg right?

    Edited by: Yukta Lolap on Oct 8, 2012 11:36 PM
  • 6. Re: Is there a datatype that allows me to store more than one item at a time
    Purvesh K Guru
    Currently Being Moderated
    You must seriously read and learn about Normalization of tables; It improves your database design (at times may increase or decrease performance, subjective cases) and eases the Understanding efforts for a new person.

    See the below tables and compare to the tables you have created
    create table customers
    (
      customer_id       number      primary key,
      fname             varchar2(50)  not null,
      mname             varchar2(50),
      lname             varchar2(50)  not null,
      join_date         date          default sysdate not null,
      is_active         char(1)     default 'N',
      constraint chk_active check (is_active in ('Y', 'N')) enable
    );
    
    create table customer_address
    (
      address_id        number      primary key,
      customer_id       number      not null,
      line_1            varchar2(100)   not null,
      line_2            varchar2(100),
      line_3            varchar2(100),
      city              varchar2(100)   not null,
      state             varchar2(100)   not null,
      zip_code          number          not null,
      is_active         char(1)         default 'N' not null,
      constraint chk_add_active check (is_active in ('Y', 'N')),
      constraint fk_cust_id foreign key (customer_id) references customers(customer_id)
    );
    
    create table customer_contact
    (
      contact_id        number      primary key,
      address_id        number      not null,
      area_code         number,
      landline          number,
      mobile            number,
      is_active         char(1)   default 'N' not null,
      constraint chk_cont_active check (is_active in ('Y', 'N'))
      constraint fk_add_id foreign key (address_id) references customer_address(address_id)
    );
    
    create table inventory
    (
      inventory_id          number        primary key,
      item_code             varchar2(25)    not null,
      item_name             varchar2(100)   not null,
      item_price            number(8, 2)    default 0,
      item_quantity         number          default 0,
      constraint chk_item_quant check (item_quantity >= 0)
    );
    You may have to improvise and adapt these tables according to your data and design to add or remove Columns/Constraints/Foreign Keys etc. I created them according to my understanding.


    --Edit:- Added Purchases table and sample data;
    create table purchases
    (
      purchase_id           number        primary key,
      purchase_lot          number        unique key  not null,     --> Unique Key to map all the Purchases, at a time, for a customer
      customer_id           number        not null,
      item_code             number        not null,
      item_price            number(8,2)   not null,
      item_quantity         number        not null,
      discount              number(3,1)   default 0,
      purchase_date         date          default sysdate   not null,
      payment_mode          varchar2(20),
      constraint fk_cust_id foreign key (customer_id) references customers(customer_id)
    )
    
    insert into purchases values (1, 1001, 1, 'AZ123', 653, 10, 0, sysdate, 'Cash');
    insert into purchases values (2, 1001, 1, 'AZ124', 225.5, 15, 2, sysdate, 'Cash');
    insert into purchases values (3, 1001, 1, 'AZ125', 90, 20, 3.5, sysdate, 'Cash');
    insert into purchases values (4, 1002, 2, 'AZ126', 111, 10, 0, sysdate, 'Cash');
    insert into purchases values (5, 1002, 2, 'AZ127', 100, 10, 0, sysdate, 'Cash');
    insert into purchases values (6, 1003, 1, 'AZ123', 101.25, 2, 0, sysdate, 'Cash');
    insert into purchases values (7, 1003, 1, 'AZ121', 1000, 1, 0, sysdate, 'Cash');
    Edited by: Purvesh K on Oct 9, 2012 12:22 PM (Added Price Column and modified sample data.)
  • 7. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    Thank you Sir, but for every customer i have to store the no of items he has purchased in a month along with the details like price, quantity etc. So how do i do that for every customer?
  • 8. Re: Is there a datatype that allows me to store more than one item at a time
    jeneesh Guru
    Currently Being Moderated
    Yukta Lolap wrote:
    Thank you so much Sir!! Your answer has really solved my issue. So i dont need to change the database design right. This is ok to move ahead to design a simple stand alone applicationg right?

    Edited by: Yukta Lolap on Oct 8, 2012 11:36 PM
    1.Your definition - ITEMPRICE NUMBER(10) - Is your price always rounded ? You cannot store 140.5 in this column.

    2.And the below definitions
    CUSTFNAME VARCHAR2(20)
    CUSTLNAME VARCHAR2(20)
    CUSTADD VARCHAR2(20)
    will the size be enough?
    3.Why are you having an ITEMQUAN column in your ITEMS table? Is this the stock?
    4. No audit columns like CRETAED_DATE, CREATED_BY, modification detais... Not required?
  • 9. Re: Is there a datatype that allows me to store more than one item at a time
    Paul Horth Expert
    Currently Being Moderated
    Yukta Lolap wrote:
    Thank you Sir, but for every customer i have to store the no of items he has purchased in a month along with the details like price, quantity etc. So how do i do that for every customer?
    You design the database relationally.

    You have a customer table holding information that is relevant to the customer only (things like name, max credit limit etc.).

    You have a stock table holding information about the things you sell (name, colour, standard price etc.)

    You have a purchases table which relates the customers to the things they bought. It might contain how many of the things
    they bought, what price thay actually paid, the date they bought it etc. For each row it would contain a foreign key (FK)
    pointing to the primary key (PK) of the customer that bought the item and another FK pointing to the PK of the stock they bought.

    Now you can answer a lot of different questions like "What did this customer buy between 1st April and 1st May?" "Who bought
    the 20 cm doodah in the last year?" etc. etc.

    Now this is a vast simplification but I hope you get the idea: you don't stuff all the information in one row: that would violate
    what's called 3rd normal form (look it up on google or read a good book on relational DB design).
  • 10. Re: Is there a datatype that allows me to store more than one item at a time
    Purvesh K Guru
    Currently Being Moderated
    Yukta Lolap wrote:
    Thank you Sir, but for every customer i have to store the no of items he has purchased in a month along with the details like price, quantity etc. So how do i do that for every customer?
    You are storing the Quantity of Purchase and Price for each Item, aren't you? (Please see the updated post, I added the Purchases table and some sample data)

    And you do not need to store the grand total for a purchase (It violates the 3rd Normal form), avoid storing the data that can be computed (unless it is highly computationally intensive); You can always sum up the total price for the entire purchase lot and display it, better you can create a View that shall do the job for you and you can just query the View and see as if you are actually storing the Grand Total.

    For more information on 3rd Normal Form, read Normalizing your Database
  • 11. Re: Is there a datatype that allows me to store more than one item at a time
    jeneesh Guru
    Currently Being Moderated
    Purvesh K wrote:
    You must seriously read and learn about Normalization of tables; It improves your database design (at times may increase or decrease performance, subjective cases) and eases the Understanding efforts for a new person.

    See the below tables and compare to the tables you have created
    create table inventory
    (
    inventory_id          number        primary key,
    item_code             varchar2(25)    not null,
    item_name             varchar2(100)   not null,
    item_price            number(8, 2)    default 0,
    item_quantity         number          default 0,
    constraint chk_item_quant check (item_quantity >= 0)
    );
    create table purchases
    (
    purchase_id           number        primary key,
    purchase_lot          number        unique key  not null,     --> Unique Key to map all the Purchases, at a time, for a customer
    customer_id           number        not null,
    item_code             number        not null,
    item_price            number(8,2)   not null,
    item_quantity         number        not null,
    discount              number(3,1)   default 0,
    purchase_date         date          default sysdate   not null,
    payment_mode          varchar2(20),
    constraint fk_cust_id foreign key (customer_id) references customers(customer_id)
    )
    It will be difficult to handle change in price...
  • 12. Re: Is there a datatype that allows me to store more than one item at a time
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    It will be difficult to handle change in price...
    I did think of that scenario and hence I added the Price column in Purchases Table;

    In an event of change of price, it shall be changed into the Inventory table and the goods that are charged on the existing Price, need not be updated. And the Grand Total of purchase can be calculated as
    SUM(Item Quantity * Item Price - Discount), grouping by the Purchase Lot.

    Assumptions:-
    Price change, if any, will not be made while a Transaction is in progress.
  • 13. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    1.I was assuming the price to be a rounded figure for now.


    2. And for storing names, they are usually 20 alphabets are enough.
    CUSTFNAME VARCHAR2(20)
    CUSTLNAME VARCHAR2(20)
    CUSTADD VARCHAR2(20)

    In CUSTADD ill just be storing the flat no , this is a monthly account system for a store who sells good on credit basis to people living in society's usually. Its actually a "kirana system". You know how kirana works right?
  • 14. Re: Is there a datatype that allows me to store more than one item at a time
    967000 Newbie
    Currently Being Moderated
    Thank you so much for clearing most of the doubts. I shall implement this asap.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points