• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 86
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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. Is it ok is the custid keeps on repeating for every item purchased by the customer in a month?
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 2
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Relax, you've already solved the problem. Any "child" table always contains the key of its "parent" table(s) in each row - the customer and items in this case - because that is how the relationship between different tables is implemented.

So your ItemPurchaseTable has the right structure. This approach is sometimes called an "intersection table", and the idea is to allow you to link data from two other tables (which have a "many-to-many" relationship) via their primary keys, which is exactly what you've done here. The customer ID is the foreign key to your customers table, and the item is the foreign key to your items table, and you need both of these in order to match a customer to the items the customer purchased in the current month.

You should probably define foreign key constraints, which will ensure that you can't add items to the Item Purchase table if they have a non-existent customer ID/item ID, and the FK constraint can also stop you deleting customers/items while they still have records in this table.

Make sure you name your columns properly e.g. in your Item Purchases table, you should make it clear that the "ITEM" column contains the ITEMID.

You should also name your tables properly. Don't call a table "ItemPurchaseTable", because we already know it's a table. And don't call a table "customerdb", because tables live inside a database. Different places use different conventions for table names, but it's common just to use the plural of whatever they contain e.g. CUSTOMERS, ITEM_PURCHASES etc.

As you've solved the problem of creating your intersection table, I suggest you take a little time to review the basics of relational data modelling, how to derive the 3rd normal form entities (tables) for your data model, how relationships are implemented via foreign keys etc.

PS: Some relational databases do in fact allow you to store multiple items inside a single column, but this is generally a PITA because sooner or later you discover lots of extra attributes you'd like to add to this "nested table", and it's much easier to do this if they're in a proper relational table e.g. suppose you wanted to record the date_purchased against your ItemPurchases.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic