1

Would someone mind advising me please regarding this table setup.

Its the first time designing a database. This will be a part of it.

Its a report writing application. Multiple Engineers can be assigned to attend any job/report and multiple engineers can author the report as well as attending.

Is this the best way to do this. I would need to be able to search attendees and authors separately in the application.

Thanks very much for the assistance.

Link to table image

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Matt
  • 23
  • 5
  • You appear to have three Staff tables. Does your design require three separate tables, or could you have a single table listing all the staff members? – O. Jones Nov 06 '18 at 10:55
  • Thanks O. Jones, Good question.... I dont want to duplicate any data of course but wasnt sure how to assign multiple authors and attending engineers to one report without the additional tables. Natural I want it as efficent as possible but not sure how. Havent identified examples quite like this in my searches... – Matt Nov 06 '18 at 11:00
  • Sounds to me you are trying to implement or going to need to implement SQL table inheritance .. https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database .. note the link has sql server tagged but still the rules will work for almost every DBMS.. In PostgreSQL you have a better solution PostgreSQL supports native table inheritance out of the box. – Raymond Nijland Nov 06 '18 at 11:38
  • Thanks Raymond. I shall invvestigate this option. Cheers Matt – Matt Nov 06 '18 at 11:40
  • Yes, you are using a common anti-pattern for a case of inheritance/subtyping. PS Hi. Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use a link/image only for convenience to supplement text and/or for what cannot be given in text. And never give a diagram without a legend/key. Use edit functions to inline, not links, if you have the rep--make your post self-contained. PS This is a faq you would find answers for if you googled many clear, concise, precise phrasings of your question/problem/goal. – philipxy Nov 07 '18 at 06:56
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Nov 07 '18 at 06:58
  • Thanks for the input Philipxy and positng advice. I did goodle for several hours, however I an new to mysql and was not sure before what I should have been googling. Following this question I now have some good avenues to research. Thanks for the assistance. Matt – Matt Nov 07 '18 at 14:25

1 Answers1

0

You have, I believe, two tables containing entities. The entities are employee and report.

These entities have two different many-to-many relationships: author and attendee.

So your tables are these

employee                report
--------                -----
employee_id (PK)        report_id (PK)
surname                 title
givenname               releasedate
whatever                whatever

Then you have two many:many relationship tables with the same columns as each other. One is author and the other is attendee.

author / attendee
------
employee_id PK, FK to employee.employee_id
report_id   PK, FK to report.report_id

Notice the compound (two-column) primary keys. 

+---------------------+\   /+-------------+\   /+-----------------------+
|                     +-----+   author    +-----+                       |
|                     |/   \+-------------+/   \|                       |
|    employee         |                         |     report            |
|                     |                         |                       |
|                     |\   /+-------------+\   /|                       |
|                     +-----+  attendee   +-----+                       |
+---------------------+/   \+-------------+/   \+-----------------------+


           \   /
           -----    means a many-to-many relationship
           /   \

When you determine an employee is an attendee for a certain report, you insert a row into the attendee table with the correct employee and report.

If you want, for example, all authors for each report you can do this sort of thing:

  SELECT r.title, r.releasedate, 
         GROUP_CONCAT(e.surname ORDER BY e.surname SEPARATED BY ',')surnames
    FROM report r
    LEFT JOIN author a ON r.report_id = a.report_id
    LEFT JOIN employee e ON a.report_id = e.report_id
   GROUP BY r.title, r.releasedate
   ORDER BY r.releasedate DESC

The LEFT JOIN operations allow your query to find reports that have no authors. Ordinary inner JOIN operations would suppress those rows from your result set.

There is a limitation with this strict E:R design. For many kinds of reports, (scientific papers for example) the order of authors is critically important. (You want to start an academic food fight? List the authors of a paper in the wrong order.)

So you author table might also contain an ordinal value.

author
------
employee_id PK, FK to employee.employee_id
report_id   PK, FK to report.report_id
ordinal     INT

and your report query would contain this line.

         GROUP_CONCAT(e.surname ORDER BY e.ordinal SEPARATED BY ',')surnames
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • Thanks very much. Im knot overly familiar with joins but wondred if the might be appropriate here. Thanks so much. I shall digest then implement this. Thanks O. Jones. I have repped you but im new here so might not show up. – Matt Nov 06 '18 at 11:39
  • Dont forget GROUP_CONCAT has a max length of 1024 chars with the default installation/configuration.. So this might not get the correct results for large tables or GROUPS. – Raymond Nijland Nov 06 '18 at 11:43
  • Thanks Raymond. Does this max legth refer to concatonated values in a string? or the number of concatoned rows that are searchable or other? – Matt Nov 06 '18 at 12:03