aspose file tools*
The moose likes JDBC and the fly likes Is there a datatype that allows me to store more than one item at a time , in a column in a row? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Is there a datatype that allows me to store more than one item at a time , in a column in a row? " Watch "Is there a datatype that allows me to store more than one item at a time , in a column in a row? " New topic
Author

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

Yuta Lolap
Ranch Hand

Joined: Jun 03, 2012
Posts: 83

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?
chris webster
Bartender

Joined: Mar 01, 2009
Posts: 1715
    
  14

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.


No more Blub for me, thank you, Vicar.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Is there a datatype that allows me to store more than one item at a time , in a column in a row?