Iam working on software for jewelery store for their book keeping purpose.
The owner sells
Sets,half sets locket set,Rings,24kt bars and so on.
Each of the above categories have Several sub categories or rows.
Like Date ID maker Type Amount adn so on.
So he wants all these with also their SALE and PURCHASE category.
Iam confused to how structure the database Should i create several databases for each Sets,Half sets and under each of them two tables of SALE and PURCHASE and those tables will have rows like ID,Type etc?
Short answer: NO.
And if you are really and truly working for a jewelry store than you need to tell them *NOW* that you do not understand databases and that you are *NOT* the person for the job. Tell them you will help them find somebody who can do the work, because you can't.
On the assumption that your tale about working for a jewelry store is just a cover for the fact that this is homework for some class: I think you need to go study your database design book(s) a lot more. First of all, there is no possible reason to have more than one DATABASE. Period. More than one table? Yes. But even then not the tables you are proposing.
You need to go look in your books for the proper setup of a classic inventory/customers/order/orderitems/vendors/purchases/purchaseitems system. That's 7 tables, if you are counting. Possibly some more, depending on what else your homework assignment is asking for, but clearly starting with 7.
Thanks for the reply it is actually an assignment.
I will tell you what are the Requirements.
The store uses a manual accounting with books.They have one Stock or Inventory account and the other is sale account.
3.Half sets and so on like 30 more jewellery items.
If they want to record sale or purchase of an item like Sets it has the following titles.
3. Amount and so on.
What im confused is with wether i can do all that one database?
I have figured out another way. Is it fine?
Nope, not even close.
I *tolld* you what tables you need:
*ONE* table for *ALL* inventory items (what you called Stock items). One.
Then when you sell items from inventory, you need a customer record (which might be a new customer or an existing one), an order record, and one orderitems record per inventory item (product id) type sold.
And similarly for when you purchase items: You need a vendor records, a purchase (or purchase order) record, and one purchaseitems record per inventory item type purchased.