0

Consider the following database example :

  • A clinichas many articles

  • The relationship between supplier and article is many to many ( (1,n) - (1,n))

    Let's say I have the clinic's id and I want to retrieve all it's suppliers, what's the best way to do it? is it by creating a "null" article for each supplier in article_supplier OR by creating a foreign key in supplier that references the appropriate clinic?

The latter solution may seem the simplest and easiest but what happens when there is a big chain of tables? Do I keep adding a foreign key each time I need a list of something? e.g :

  • list of medicines a clinic uses
  • list of prescriptions a doctor gave
  • ...

If it makes a difference, I am using Laravel's Eloquent ORM

Ali BAGHO
  • 358
  • 6
  • 17
  • What are the lines between clinics & articles supposed to mean? What then are the others supposed to mean? What reference are you using for diagramming? What reference are you using for *designing*? PS Diagrams are a nice extra but [please always include all text as text not as images/links](https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). – philipxy Sep 23 '17 at 23:34

2 Answers2

1

A table represents a relationship among values. (Some values identify entities.) A database can't be used until we are told what each table--base or query result--means: what business/application relationship its rows satisfy. (Ie, what is its (characteristic) predicate.) What are the relationships for your tables?

To query we express a relationship in terms of base relationships then express its table in terms of the corresponding base tables. Eg a join returns rows satisfying one relationship and another. So again, we need to know tables' relationships in business/application terms.

Cardinalities & constraints are properties of relationships given what situations can arise. They aren't needed to update or query. They can guide design & are used for integrity.

When, given a clinic, you talk about "its" suppliers, you do not say what you mean. "Has", "its", "for", "references", "appropriate"--all mean nothing--they refer to related entities, but they don't say how they are related in terms of the business/application. This design contains no explicit relationship on clinics & articles. If it did, you've said nothing by which we could put the right rows in or see the rows & know about the situation. Still, you could then derive clinic-supplier rows where the supplier is "for" some article a given clinic "has". But is that the relationship you mean?? Eg if you want pairs where the clinic is allowed to "have" the supplier "for" some articles, that's a new relationship/table that cannot be derived from what you have.

creating a "null" article for each supplier in article_supplier

That relationship/table is a certain combination of article_supplier & the relationship/table just described. But it is simpler to just have those two.

creating a foreign key in supplier that references the appropriate clinic

That would mean that if a supplier "has" more than one clinic then there can be rows in the new version that differ only by all the other columns; normalization theory says that's worse than the original design and a clinic_supplier relationship/table. And it means that if a supplier "has" no clinic you would need something like a nullable FK (foreign key).

So you likely want a clinic_supplier table. But you should post a new question in which you actually say what business/application relationships you are talking about.

Your question & the following are essentially duplicates in that the basic principles/notions to obviously apply to answer them are all the same:
How do I find relations between tables that are long-distance related?
Required to join 2 tables with their FKs in a 3rd table
Best Solution - Ternary or Binary Relationship

You need to read an information modeling & database design textbook.

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • 1
    The last sentence says it all. Database ddesign is about more than figuring out some syntax. – Walter Mitty Sep 24 '17 at 13:17
  • [It used to be the first!](https://stackoverflow.com/revisions/46385211/1) I don't understand what you are trying to say about "syntax"; I'm not using "predicate" in the sense of "expression" but in the sense of "meaning". (Denoted by some expression, of course.) My answer is all about semantics. – philipxy Sep 24 '17 at 13:44
  • 1
    My comment was directed to the OP, and not to your response. I agree wholeheartedly with your response. – Walter Mitty Sep 24 '17 at 19:11
  • both your question were very helpful, thank you ! i always try to make my question as simple as possible but every time i mess something up. what i failed to add is : - I forgot to add the `FK` in article - I am using Eloquent's logic (HasOne, BelongsTo .. ) to express the queries i will need - this is a small clinic program and having a supplier that supplies to many clinics is not needed what is needed is the following: - get the list of articles that belongs to a clinic (expressed) - each article can be supplied by 1-n supplier (expressed) - get the list of suppliers (NE) – Ali BAGHO Sep 24 '17 at 19:53
  • I can eventually just add a `clinic_FK` in supplier and get the list easily, and my question is : is it okay to do that or should i redo the thing because if i fall in this problem then the design is just wrong and I am missing something else? – Ali BAGHO Sep 24 '17 at 19:55
1

In your design : Supplier is a table. Articles is a table. Since Supplier supplies articles, their relationship is expressed in terms of a table that links them. So, there is a supplier_article table.

Clinic is a table. You mention Clinic uses articles. By the same principle as above, the relationship between Clinic and Article should be expressed in the form of a table. This should be clinic_article table.

Though you haven't mentioned, you must look at other dependencies that arise. For example, a clinic performs procedures and procedures use articles.

by creating a foreign key in supplier that references the appropriate clinic

This would assume that one supplier can supply only to one clinic forever. Even if this is true today, it won't be true tomorrow, because you might want to plan for multiple clinics in the same location or using the same application or database.

Whirl Mind
  • 884
  • 1
  • 9
  • 18