Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Unanswered: appreciate help with relationsship tables

    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 tables created are

    *ClienBuyerDetails (customer details)
    *ClientSuppliersDetails(SupplierDetails)
    *Products (supertype)
    *Laptop (subtype)
    *PC (subtype)
    *Printer (subtype)
    *TFT (subtype)
    *generalwaste

    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 and will be therefore sent to waste.
    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 if my relationships are incorrect.

    I have added attachment of the relationship table.

    I have created two new association tables to contain the m:m relationship between clients and products and also same with generalwaste (not sure if correct)

    Thank you very much : )
    Attached Thumbnails Attached Thumbnails database relationships asccpsiation tablews.JPG  

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    This is coming along really well.
    Drop the Primary keys for the association tables and make the two foreign keys a composite primary key. If you think about it then it makes sense - under what possible circumstance will you ever, ever need to use the value of ClientBuyersProductID? The answer is never!

    Regarding the removal - this is not a m:m relationship. A product can only be removed once right? I also assume a removal only applies to a single product, but you'd need to tell me.

    As such, you have two options. The strictly normalised design is link GeneralWaste to Products - it is a 1:1 relationship. Just use removal date, time and person - the existance of a row indicates the table has been removed.
    Alternatively, just move these columns into the product table - if they are NULL, the product has not been removed. If they are not null then it has been removed.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    18
    Thank you for the reply

    i have now removed the "Generalwaste" Table and instead moved the field of that table into the "producttable". I like the idea of what you suggested about make the fields null (not sent to gen waste) and NOT null (sent to genwaste). I have removed the primary keys of the association tables and now made a composite keys for the both tables.

    I hope its correct

    Would i now start to develop the form if the the relationships are correct?

    Ps i am now using access 2007 takes a little time getting used to from the 2003 version

    Thank you for your help
    Attached Thumbnails Attached Thumbnails database relationships composition key and remve  genwaste table.JPG  

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would get rid of "Has it been removed" and "remove to general...". If there is a date then it has been removed. These are transitive dependencies (look up third normal form on google).

    I would ask yourself a few questions regarding the sales and purchases, for example:
    Are all you ever going to want to record other information regarding sales and purchases (dates, tax etc)?

    Also, now I've looked more closely at this I may have given you some bad advice. Your product table records specific product instances. As such, each product can be sold and supplied only once, not many times. The association tables are not required in this case. Or you could make the model table generic and include the serial number in the association table instead - this will result in less products in the product table. It depends if you need a record of serial number etc. for items you have in stock, or only at the point of sale.

    We have concentrated on the products so far, but there is still lots to do. I would google and look at some invoice databases for ideas. I don't think
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Posts
    18
    I have now removed the association tables. I have also included a employers table. I would love to and other details regarding tax and dates etc, but not quite sure how to do it. Also thought i would need a method of payment select i.e visa, cash, debit. I have added several table, is this correct?

    Another concern is for instance if supplier supplied 300 Pc's , it would be a pain entering the data 300 times or if it were any other product. Or if a customer wanted to purchase 200 Pc's. I didn't expect it be this difficult .

    I am trying to find a template database similar to what i need however there seems to be nothing which includes suppliers info and customer purchasing, theres only customer purchasing databases.


    I want make one simple database for suppliers details and products their supplying and customer purchasing the products. And then later will improve database including payment methods, tax etc. At moment i want a simple type database. I have attached 2 relation ship tables one simple and one with tax, payment method etc.
    Attached Thumbnails Attached Thumbnails database relationships deleted association tables and added new tbales.JPG   relationships deleted asscociation tables.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •