How Many Relationships?

[JAA149]

Hi Mates,

I will just get right down to it. If I have these Entities/Tables.

1 - Clients
2 - Cases
3 - Invoices

Assuming all relationships are one-to-many. Which Figure is the right way.

Figure A - I can deduce which invoice is for which case, which case is for which client and therefore which invoice is for which client.

Figure B - I can not find out which invoice is for which Case. To solve this I have to make Figue C

Figure C - But this has now 2 relationships.

Is the Figure A not more accurate than Figure C

[pclfw]

This type of question can most easily be answered by normalising the data.

Correct nomalisation will show which of these entities 'owns' the others. For instance Can a CASE have more than one CLIENT? I expect that a CASE may have more than one INVOICE, but does an INVOICE get raised for more than one CASE & is an INVOICE sent to more than one CLIENT?

Once you have examined the relationships between your company processes and the data you use you will be able to develop the correct relationship diagram for the entites that you have shown.

BTW I think that none of the diagrams in your example are correct. I would epect a MANY to MANY relationship to exist between all of these entities.

1) A CLIENT may be involved in many CASES

2) A CLIENT may receive many INVOICES

3) A CASE may have many CLIENTS

4) A CASE many have many INVOICES

5) An INVOICE may reference many CASEs

6) An INVOICE may be sent to many CLIENTS

5 & 6 depend upon your local procedures and processes so they may not be true, but overall these relationships need to be defined so that you can answer your own question.

[Fest3er]

Clients never reference cases or invoices.

Invoices can be related to many clients. Invoices can be related to many cases.

Cases can be related to many clients. Cases can be related to many invoices.

So:

• Clients, cases and invoices never reference each other.
• Case relations reference many clients.
• Case relations reference many invoices.
• Invoice relations reference many clients.
• Invoice relations reference many cases.

If you want a 'picture':

table Clients
    key CLI_ID
    char Name

  table Cases
    key CAS_ID
    char name

  table Invoices
    key   INV_ID
    date  Date
    float Amount

  table CaseClientRelate
    ptr CAS_ID
    ptr CLI_ID

  table CaseInvoiceRelate
    ptr CAS_ID
    ptr INV_ID

  table InvoiceClientRelate
    ptr INV_ID
    ptr CLI_ID

You should be able to report lists of clients, cases and invoices with all six orderings possible with this. And you can find all related info given one of any client, case or invoice.