database relations

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 ...?

Answer 1

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.

Answer 2

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

Answer 3

I agree Many thanks ! The oeople that are using the database complain that it gets stuck often ..... Can this bad relationship be the cause ?

Answer 4

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

Answer 5

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

Answer 6

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