Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2009
    Posts
    18

    Unhappy Unanswered: Improved Relationship tables, is it correct?

    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)
    *invoice
    *invocedetails
    *employerdetails
    *payments
    *payementsmethods


    The suppliers will be supplying laptop,pc,printer , tft and customer will purchase the products.

    Im lost with the relationship tables as 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 relationships tables, please could you tell me if this is correct and if not what i need to do .
    Attached Thumbnails Attached Thumbnails FINAL relationships rable.JPG  

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I don't have time at the moment to look into your particular situation (maybe someone else might), but I can point you to Paul Litwen's article; it's kind of the unofficial standard on relation database design around here. HTH. Cheers!
    Me.Geek = True

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Your naming scheme is not very coherent:
    -Arbitrary use of capital letters (HardDiskSpace versus HardDriveAVAILABLE versus FLashdiskdrive)
    -Different names for the same kind of data in different tables (Forename versus ClientForename)
    -Some names are misspelt (Clienbtsurname, Mobiklenumber)
    -Use of characters that can cause problem (Make/Model, Company name)
    -Some tables seem to be useless or redundant (a laptop is a PC. Is it necessary to have two tables? Could a single table with a column specifying if it's a laptop or a desktop be enough? Clients and Suppliers could probably be grouped in one table too.)
    -What's a ClientSupplier?

    2. There are probable flaws in the organizational schema itself:
    -Payments should normally be linked to Invoices and not to Clients
    -Some data, related to a credit card, should probably be better stored in a seperate table, except if all customers pay by card.

    These are mainly suggestions; it's difficult to know how your want your system to work with only a schema.

    Apart from a schema, what do you have so far? A good data model is an important part of a database application but it needs a lot more to have it work.
    Have a nice day!

  4. #4
    Join Date
    Aug 2009
    Posts
    18
    I have changed the the minor details as you have suggested. I did not make laptop and pc in one table as the laptop include screen size but the PCtower doesn't have screen. I did not understand what your meant about placing some credit details o new table. Is the relationship now ok? Do i still have alot to do regarding the tables relationships? please help, i have spent most of over a month trying to correct relationship tables however i'm always back to square 1 .

    see attachment please below

    thank you for the help
    Attached Thumbnails Attached Thumbnails finale relationshiptables.JPG  

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Aug 2009
    Posts
    18
    sorry this is the new thread, the other one was old sorry.

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No need for apologies, I mainly linked to it so related information could be found
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Aug 2009
    Posts
    18
    thank you star

    I have made minor changes to the relationships. I have now completely removed table credit card details and tables associated with it as it got to complicated. I have decided to make payment by Cash only or by cheque.

    Is the relationships correct now? To me it looks correct

    Thank you for your help
    Attached Thumbnails Attached Thumbnails finaled re;ationships no credit details.JPG  

  9. #9
    Join Date
    Aug 2009
    Posts
    18
    Sorry to keep being repetitive, It looks correct to me now, but im a novice. Can anyone please tell me if it is correct and give advice on what i need to do?

    Thank you , so much

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You mix elements of data that do not pertain to the same register, e.g. you mix data about a person (forename, surname, address etc.) with data such as ProductID in CustomerDetails. You should have one table for People (employees, customers, suppliers) and not three separate tables: EmployerDetails (should it not be EmployeeDetails?), CustomersDetail and SuppliersDetail. An employee can also be a customer etc. Also, why Customers and Suppliers: plural and Employer (or Employee): singular?

    There should only be one table for both Desktops and Laptops. Your explanation about a laptop having a screen and then needing a field to hold the screen size does not stand: You create two tables instead of one just to avoid a field (ScreenSize) that will remain empty sometimes. With such a logic you could create several tables for SuppliersDetails, for instance: one when you only need two lines for the address and one when you need three lines, one for suppliers that have a mobile and one for suppliers that don't etc.

    In PaymentsDetails you should not seperate PaymentDate and PaymentTime: There is only one type of data in Access that stores both date and time. If you need to separate date from time this should be done in a form or in a report using a function such as Format(). The same is true for CollectedDate and CollectedTime etc. in Products.

    Whith your system how do you handle this case: I go with my sister to buy a new PC for my mother. Both my sister and I pay half the price of the new system (by card) but the invoice has be be established in our mother's name, as she will be the owner of it? I do not exagerate: such cases exist for real.

    For a better understanding of databases normalization design, see:
    The Relational Data Model, Normalisation and effective Database Design
    database design - About.com : Databases
    General: Commonly used naming conventions
    Roger's Access Library: Database Design
    Have a nice day!

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

    You should have one table for People (employees, customers, suppliers) and not three separate tables: EmployerDetails (should it not be EmployeeDetails?), CustomersDetail and SuppliersDetail.
    I have changed the tables so employees, customers and suppliers are in one table instead of three separate tables. However I dont understand for instance if i wanted to make a report to find all employees or find all customers who bought printers, how would the system distinguish who is an employee from suppliers from customers? And in the forum when employee is dealing with customer purchasing products how will i be able to select the employee dealing with the customer order for proof of which employee dealt with the customer at that time, how will the system distinguish the employers from the customer and suppliers?

    In PaymentsDetails you should not seperate PaymentDate and PaymentTime: There is only one type of data in Access that stores both date and time. If you need to separate date from time this should be done in a form or in a report using a function such as Format(). The same is true for CollectedDate and CollectedTime etc. in Products.
    I didn't quite understand this. I took out the time collected, timeremoved and timeofpayement fields. I instead changed them to include date and time in the same field, by going in the table design and customizing the input mask for that field. This is shown in the attachment below. is it correct and is it what you ment?



    Whith your system how do you handle this case: I go with my sister to buy a new PC for my mother. Both my sister and I pay half the price of the new system (by card) but the invoice has be be established in our mother's name, as she will be the owner of it? I do not exagerate: such cases exist for real.
    Im not going to have an option of payement by credit cards, instead will take cash only. This is because its too complicated , hence the scenario you had given totally puzzled me to what i could do to resolve the problem. Hopefully by accepting cash only i wouldn't have a problem.

    thank you for your help
    Attached Thumbnails Attached Thumbnails relationships tables peopletable and pclaptop.JPG   date-time input masks.JPG  
    Last edited by Drdatabase; 09-15-09 at 19:23. Reason: forgot to attach pictures

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. As far as employees are concerned, you would need an Employees table anyway because for an employee you need more than just general information such as name, address etc. Yous would also need a social security number, a hire date and other specific information. So, your Employee table would have columns for such data plus a foreign key pointing to the People table.

    Retrieving all employees could then be easily done with a query such as:
    Code:
    SELECT * FROM Employees INNER JOIN People ON Employees.PeopleID = People.PeopleID;
    You could add a WHERE... criteria if you wanted to find one or several employees based on a specific condition.

    IDs for the suppliers are stored as foreign keys in the Products table, so the same kind of JOIN query would allow to retrieve the supplier for a given product. If necessary you could extent this model and also have a table of Suppliers (if you need to store specific information in case of a supplier) etc.

    In your Invoices table you need at least two columns containing a person foreign key: one for the the vendor (EmployeeID) and one for the customer (PeopleID). That way, a JOIN query would allow to retrieve information about the customer and the vendor for a given invoice.

    The general idea here is to avoid duplicated data and to have every table at least in the first normal form: this is a minimum and having tables that do not comply (at least) to the fist normal form is begging for troubles sooner or later.

    2. Storing the date and the time separately would mean that you would have two columns to store one part of the same data in each, as the is no different specific data type for a date and for a time.

    3. You could easily solve the little problem I submitted by using the PaymentDetails table: an invoice can have more than one payment. You simply need to add a column AmountDue that will hold the total amount due for an invoice.

    (I do not choose this example randomly. It really happened to me several months ago, except that it was for a TV set we wanted to buy for our mother's 70th birthday. My sister and I went to a mall where several shops selling electric and electronic appliances can be found, and in the first shop we entered, we were told that it was not possible to have an invoice with a different name than the one of the buyer and that it was not possible to pay with two different credit cards. My sister and I left that shop and bought the TV set somewhere else and the first vendor lost a sale of 1199 Euro!)
    Have a nice day!

  13. #13
    Join Date
    Aug 2009
    Posts
    18
    Hi

    thank you for the detail explanation and advice, very much appreciated

    1. As far as employees are concerned, you would need an Employees table anyway because for an employee you need more than just general information such as name, address etc. Yous would also need a social security number, a hire date and other specific information. So, your Employee table would have columns for such data plus a foreign key pointing to the People table.

    Retrieving all employees could then be easily done with a query such as:
    Code:

    SELECT * FROM Employees INNER JOIN People ON Employees.PeopleID = People.PeopleID;

    You could add a WHERE... criteria if you wanted to find one or several employees based on a specific condition.

    IDs for the suppliers are stored as foreign keys in the Products table, so the same kind of JOIN query would allow to retrieve the supplier for a given product. If necessary you could extent this model and also have a table of Suppliers (if you need to store specific information in case of a supplier) etc.

    In your Invoices table you need at least two columns containing a person foreign key: one for the the vendor (EmployeeID) and one for the customer (PeopleID). That way, a JOIN query would allow to retrieve information about the customer and the vendor for a given invoice.

    The general idea here is to avoid duplicated data and to have every table at least in the first normal form: this is a minimum and having tables that do not comply (at least) to the fist normal form is begging for troubles sooner or later.
    That makes clear sense now that you look at it like that. I can now see why having 3 different tables for customers, suppliers, and employee was bad idea due to duplicate data thank you. Also it now makes sense on how to retrieve specific details regarding customers, employees or suppliers.

    2. Storing the date and the time separately would mean that you would have two columns to store one part of the same data in each, as the is no different specific data type for a date and for a time.
    Is it correct what i have done thus far in the tables regarding the date and time?


    3. You could easily solve the little problem I submitted by using the PaymentDetails table: an invoice can have more than one payment. You simply need to add a column AmountDue that will hold the total amount due for an invoice.
    Lol didnt expect the solution to be that easy by adding a new column "amountdue" . thank you


    Is the relationships correct now? Am I ready to start making forms and queries, cant wait to start making querys and designing the forms
    Attached Thumbnails Attached Thumbnails relationship tables added employee table.JPG  
    Last edited by Drdatabase; 09-15-09 at 22:47. Reason: forgot to add picture

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I didn't look at the specifics on relationships and if they are correct or not (or fit into your scope of the program) but I have to make a comment about the table/field names.

    It's nice to see table and field names which don't comprise of spaces, reserverd words, or other odd characters!

    ID field names match ID field names in relational tables which makes troubleshooting much more efficient.

    Nice job on the naming schemes!
    Last edited by pkstormy; 09-15-09 at 23:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    Aug 2009
    Posts
    18
    Thank you very much for the reply. When i look at my first version of the relationships tables it looked all over the place and had many errors. Thanks to this site and people on here im learning and understanding MS Access database a whole lot better, thank you.

    Im just hoping that this relationship table are correct and i have included information correctly, so I can begin to make queries, forms and reports which will be my next challenge.

    I hope its correct?

    thank you

Posting Permissions

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