• Post Reply Bookmark Topic Watch Topic
  • New Topic

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

Ranch Hand
Posts: 86
Eclipse IDE Java jQuery
  • Mark post as helpful
  • send pies
  • 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


Customer Database:
SQL> desc customerdb;
Name Null? Type


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;


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 :

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?
Posts: 2407
Linux Oracle Postgres Database Python Scala
  • X Likes 2
  • Mark post as helpful
  • send pies
  • 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.
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!