Unanswered: Having diffculties with relation ship tables (attached doc)
The system i am trying to create is a warehouse type system where suppliers details are kept along with the products they are supplying. And at same time keep records of the purchasers (customer) details and the products they have bought.
The suppliers will be supplying laptop,pc,printer , tft and customer will purchase the products. The general waste is the products supplied that are not working.
Im lost with the relationship tables and i dont know if they are linked correctly. I am not sure weather im missing a table or my relationships are incorrect.
I have added attachment of the relationship table.
If I were you I would look up modelling technique called subtype-supertype.
The idea is that you have a table that contains all the attributes common to all your products (manufacturor, serial number etc) aka the supertype. You then create a table for each product type that stores the product specific info (screen width, dpi, processor speed etc.) aka the subtypes and relate these to the super type. You now relate the super type to the other tables (client tables and general waste), simplfying the design.
1) If you need to add a new common column, you add it in one table only.
2) You only have a single relationship from the supertype to those three tables. If you add another product you don't need to add a new column to those three tables.
Thank you very for the reply, that was was brilliant advice and help. I have now changed the tables and formed new relationships, as shown on attachment.
I am confused as to how i make the forms for the end user to enter the details.
I would need a client supplier form inputting their details and the product details they are supplying. how would i be able to put together client supplier details, Sub type tables and super type tables at one form for the end user to use for supplier.
And similar for the customer form purchasing the products, i would need to be able to input customer details, Sub type tables and supertype tables in one form. And Also same for general waste.
Thanks for your help
Last edited by Drdatabase; 08-28-09 at 15:11.
Reason: forgot to add attachement
Good job - I didn't expect you to crack that on so little info.
Minor points: there is no need for those primary key columns you have in your subtype tables. Use the supertype's primary key as their primary key. These are 1:1 relationships, so they should share primary keys.
I would have called the supertype table "products" or something - what happens if you need another supertype and subtypes in your database (e.g. for you clients?). Similarly, "subtypeprinters" just printers. It is a modelling technique - you don't need to name the tables after it!
Anyhoo - to answer the question I would have a main form, linked to the supertype table with all the common fields on it. Create an input form for all the subtypes, containing their respective fields. Create a subform on your main form. Based on the product type the user selects, use VBA to programmatically change the form contained in the sub form to the relevant subtype. If you set up the properties correctly this will look really seamless.
I've reread your question - that covers inputting products.
I think there is further work to do with your design. You currently have a 1:m relationship between products and clients. This means you would enter ALL the clients details for each product they are associated with. This is a problem. You should remove the product id from both tables and create two new association tables to contain the m:m relationship (google these terms if you don't get it).
Hi i have now changed the tables names as you have suggested and deleted the primary keys in the subtypes replacing them with the supertypes.
I have created three new association tables to contain M:M relationships. I have created two for clients and one for the generalwaste table as i thought you would need M:M relationship here to (im not sure if the was correct thing to do). Im new to creating association tables, so did a bit of reading into it, and now have created the new relationships as shown on the attachment. Is it correct?
I really appreciate the time and help you are giving thank you.