none
database relations RRS feed

  • Question

  • i have this database i inherited .

      it is about customers (Klanten) that order parties (Feesten) (1 to many to FSklant)

    of course the party is not always at their home , it can be on location (Klanten-1) (1 to many FSadres)

    for one or other reason , they put the locations and customers all together in the Customersfile.  1 reason i can think of is that a lot of the data needed is the same:  if the party is on location or not :(like is their an oven available, water, dishwasher  ....)

    but is that the way to do it ?  i would have separated the two files . and can this way be a problem for corruption , performance ...?

    Thursday, May 19, 2016 6:03 AM

Answers

  • Each distinct thing should be in a separate table. So you need tables for Customers, Locations and Parties.

    I would think a party would have one location and one customer.

    And a location and a customer could both have many parties.

    • Marked as answer by tekoko10 Thursday, May 19, 2016 10:00 PM
    Thursday, May 19, 2016 8:17 PM
  • In relational database terms Parties is modelling a many-to-many relationship type between Customers and Locations.  In broad outline, therefore the tables would be like this:

    Customers
    ….CustomerID  (PK)
    ….FirstName
    ….LastName
    ….etc

    Locations
    ….LocationID  (PK)
    ….Location
    ….etc

    Parties
    ….CustomerID  (FK)
    ….LocationID  (FK)
    ….PartyDate
    ….etc

    The primary key of the last table is a composite one made up of the two foreign keys and the PartyDate column.

    Ken Sheridan, Stafford, England

    • Marked as answer by tekoko10 Thursday, May 19, 2016 10:00 PM
    Thursday, May 19, 2016 9:14 PM
  • That opens a whole new can of worms, which should be the subject of a new thread.  You certainly need to improve the logical model in the way I described, but the operation of the database online is a different matter.

    Ken Sheridan, Stafford, England

    • Marked as answer by tekoko10 Friday, May 20, 2016 12:06 PM
    Friday, May 20, 2016 11:28 AM

All replies

  • Each distinct thing should be in a separate table. So you need tables for Customers, Locations and Parties.

    I would think a party would have one location and one customer.

    And a location and a customer could both have many parties.

    • Marked as answer by tekoko10 Thursday, May 19, 2016 10:00 PM
    Thursday, May 19, 2016 8:17 PM
  • In relational database terms Parties is modelling a many-to-many relationship type between Customers and Locations.  In broad outline, therefore the tables would be like this:

    Customers
    ….CustomerID  (PK)
    ….FirstName
    ….LastName
    ….etc

    Locations
    ….LocationID  (PK)
    ….Location
    ….etc

    Parties
    ….CustomerID  (FK)
    ….LocationID  (FK)
    ….PartyDate
    ….etc

    The primary key of the last table is a composite one made up of the two foreign keys and the PartyDate column.

    Ken Sheridan, Stafford, England

    • Marked as answer by tekoko10 Thursday, May 19, 2016 10:00 PM
    Thursday, May 19, 2016 9:14 PM
  • I agree Many thanks ! The oeople that are using the database complain that it gets stuck often ..... Can this bad relationship be the cause ?
    Thursday, May 19, 2016 10:04 PM
  • The oeople that are using the database complain that it gets stuck often ..... Can this bad relationship be the cause ?
    Without knowing in exactly what way it 'gets stuck' it's hard to say, but the database relational model is a formal one based on formal principles.  Departure from these principles will principally reduce the integrity of the database, but might also affect its efficiency.  Other factors could be relevant, however.  Is the database split into separate front and back end files for instance?   If not, and multiple users area accessing the database simultaneously, problems are very likely to arise. 

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, May 20, 2016 11:00 AM Typo corrected.
    Friday, May 20, 2016 10:59 AM
  • if it is stuck , nobody can do anything anymore except going out of the database and all start over again.

    i did not split up the database, because of this question i asked : question splitted database

    Friday, May 20, 2016 11:21 AM
  • That opens a whole new can of worms, which should be the subject of a new thread.  You certainly need to improve the logical model in the way I described, but the operation of the database online is a different matter.

    Ken Sheridan, Stafford, England

    • Marked as answer by tekoko10 Friday, May 20, 2016 12:06 PM
    Friday, May 20, 2016 11:28 AM