-1

Say I have a table lawareas for areas of law, like:

ID Area
1  Children Law
2  EU law
3  Crime

And also lawyers, but each lawyer can be assigned multiple law areas, so the table is like

ID Name Areas
1  John 1,3
2  Bob  2,3

This is the way I am doing it now, but it seems wrong.

Is there a better way to structure this data?

I guess I could have a third table with relationships between the above 2 tables such as:

AreaID LawyerID
1      1
3      1
2      2
3      2

Which is better and why, the original option seems like less hassle to me in every way?

philipxy
  • 14,867
  • 6
  • 39
  • 83
dzoniboj
  • 39
  • 6
  • definitely the later. go for it. the first one will give you a lot of headaches when trying the fetch and work with it. – Jorge Campos May 05 '18 at 11:18
  • One lawyer can be assigned to many law areas and one law area can have many lawyers assigned to it. This is just another way of saying that there is a many-to-many relationship between lawyers and law areas. A many-to-many relationship requires a third table, a linking table, to establish that relationship. The linking table will always contain the primary keys of the two tables being linked. The 2 primary keys can usually be used as the composite primary key of the linking table. Each of the primary keys in the linking table is a foreign key pointing back to the original table. – Honeyboy Wilson May 05 '18 at 13:34
  • Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) – philipxy May 05 '18 at 15:41
  • Hi. Your question shows you are lacking fundamental knowledge. Read a textbook on information modeling, the relational model & database design. Also this is a faq that you could easily google. Always google many clear, concise, specific versions/variants/phrasings of your question/problem/goal/desiderata with & without your specific strings/names/code and read many answers from many questions, which should inform your further googling. If you don't find an answer after applying what you learned and repeating this then ask a question. Use the best search as a title. PS See [ask] and the [help]. – philipxy May 05 '18 at 15:46

1 Answers1

0

In the first solution, you are expressing data semantics through string concatenation. This has serious drawbacks:

  • it will be cumbersome to query data (e.g. get all lawyers who exercise crime law)

  • adding and removing areas to lawyers is cumbersome (since you have to do string parsing and concatenation)

  • you have to take care for data consistency yourself (e.g. all area ids must exist in the area table)

Basically, you lose all functionality that your DBMS offers, except for general data storage and retrieval. You will end up with horrible application code where you re-implement this functionality, bad performance and most likely data inconsistency.

Let the database do what it can do for you, but you have to accord to its structural demands for that.

Moritz Ringler
  • 9,772
  • 9
  • 21
  • 34