Hello all,
Just starting out on building db to manage production/ordering etc. from initial customer enquiry through to delivery of finished product. Very very rusty with Access and dbs as I have only built a fairly simple one many many years ago (in fact my hubby seems to think it was on floppy but not sure I agree!!). After staring blankly at my new empty Access screen a few weeks ago I tentatively started. Weeks on........ having now read what feels like millions of forum threads, buying a book, doing some online tutorials and playing around with several rough play copies I am now ready to start in earnest!
I am aware of the importance of getting the underlying structure and relationships correct in the beginning and so on this note could someone kindly cast an eye over the info below to see if I am on the right track?
Customers can also be suppliers (and obviously vice versa). Customers can also be manufacturers - although normally not dealt with directly but through a supplier (who can also be a customer - or a manufacturer!). Manufacturers can ship directly to us although the products are bought through the supplier. Manufacturers can also be suppliers. We also sometimes ship directly to our customers' customers - who for want of a better description I am referring to as "third parties" for now. We can use designs provided by our customers or by their customers (third parties). We request technical information from manufacturers - but buy through the suppliers in most cases (but not necessarily all). Confused yet? Hee hee - I certainly was! I have so far come up with this:
CompanyType: Customer, Supplier, Manufacturer.
AddressType: Customer delivery, Third party delivery, Supply outlet. (Both these have the potential to grow - eg Accounts office address or whatever I find I need)
Suppliers, manufacturers and customers are lumped together in one "companies" table as they share the same attributes at company level (ie one website - one set of notes relating to the company etc....) with a "company details" table that defines their various roles.
Customers' customers (my "third parties" for now) have been put in a separate table to the rest as I could not figure out how to relate them to our customer if they were in the main "companies" table. (table referring to itself???)
This is only the beginning bit - which I wanted to get as right as I could before moving further on with my tables/relationships.
Many thanks in advance for all constructive criticism!!