CODEWITHSUNDEEP
mysqlmysql-workbench
GNov
GNov

Reputation: 105

Crows Foot Mysql Relationships

I am using MySQL workbench to build a database for a client.

In this circumstance a ticket can have many numbers but a pool can have many tickets.

I am not sure if i have set this up correctly.

I can see that the crow foot which links the "Tickets" table to the "TicketNumber" table has a one-to-many relationship. Does this mean a ticket will have many ticketnumbers?

I created a FK in the "TicketNumbers" table which links to the Tickets table using the "ticket_id" column as a reference.

For the pools i created a FK in the "Pools" table which links to the "Tickets" table using the "pool_id" as a reference. It appears that the crows foot indicates that a ticket can have many pools but this is not the case?

Have i done this correctly or should i invert the relationships by creating the FK in the opposite tables?

Tickets System

Upvotes: 1

Views: 2549

Answers (1)

Jacques Amar
Jacques Amar

Reputation: 1833

I hate to answer about GRAPHICAL things :-)

The crows feet with solid line indicate IDENTIFYING relation from the single line to the multiple (triangle or crows feet) line. This means that the field linked to is part of the PRIMARY KEY of the receiving table.

A dashed line means a NON IDENTIFYING relationship. Means that the linked field is NOT part of the PRIMARY KEY.

In your case, the relationship should be FROM Pools TO Tickets, you relationship is reversed and should be dashed (non identifying).

The relationship between Tickets and TicketNumbers seems correct. It's not my favorite way to do things as I find the number_id field to be useless. I would have a table with ticket_id and number only and both fields used to form the PRIMARY KEY , in which case, the relationship should be IDENTIFYING (solid line) between Tickets and TicketNumbers. This is more art and science and opinions will differ.

Upvotes: 3

Related Questions