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.
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?
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.
subject: Is there a datatype that allows me to store more than one item at a time , in a column in a row?