I need help in designing a database model for my company.
basically it is a trading company into industrial products. so we have sales,purchases,inventory,warehouses,employees,receivables,payables, etc. as main functions.
I would greatly appreciate someone's help or guidance on how to go about it. i have made the following tables:-
invoice no(PK), salesmanid, customer code, customer name, voucher type, invoice date, invoice amount, warehouse id
invoice no, itemcode, sale qty, sales price
itemcode(PK), item name, qty in stock, cost price
customer code(PK), customer name, customer address, salesman id
i need help with defining proper keys and advise on adding/removing coloumns, etc.
My best advice: If you have to ask how to design a receivables table, you probably shouldn't be writing the receivables system. I've designed enterprise systems with over a thousand tables and I wouldn't willingly tackle an accounting system. With most custom development the system you deliver defines the business process to some degree. Your way can be slightly different than the designers envisioned and still be better.
With accounting software, there is One Way It Should Be Done. Your absolute best case is "we didn't screw it up".
Inventory, on the other hand, is usually domain-specific enough that it warrants custom coding. So: Why one big text field for address? Don't you need to know what state they're in (maybe not)? Do you analyze shipments by zip code? Do they have a phone number you care about? Customer name is often separated into first and last (at a minimum).
You might want to hold off on the database design until you know exactly (or mostly) what you're going to do with the data. Mock up a few reports, and a fake email cmpaign. Sketch out some interface screens. Then you can come back to the DB with a clearer idea of what you need. It does you no good to create an overly generic design upfront, and then have to keep modifying it. Of all the things you can change in your application, the DB is probably the toughest, so I'd try to work out the kinks in other areas first.