在其他表中使用关系表的ID [英] Using relationship table's ID in other tables

问题描述

我有2个使用关系表(ManyToMany)关联的实体，然后有一个定价表，其中包含医院服务的定价信息.如果链接到该服务的医院不同，则该表对同一服务的定价可能会不同.

I have 2 entities which are related using a relationship table (ManyToMany) And then I have a pricing table which has pricing information for a service of a hospital. This table can have different pricing for the same service if the hospital linked to the service is different.

hospitals:
id name 

services:
id name

hospital_service:
hospital_id service_id

service_pricing:
id hospital_id service_id weight_range_id LH_price SH_price

service_duration:
id hospital_id service_id weight_range_id LH_duration SH_duration

现在我的问题是我应该重写service_pricing表中的hospital_id vs service_id信息吗?这不是重复数据吗?我已经在Hospital_service表中拥有此信息.

Now my question is should I be rewriting the hospital_id vs service_id information in the service_pricing table? Isn't this repetition of data? I already have this information in Hospital_service table.

(与service_duration表相同的问题)

(Same is the issue I have with service_duration table)

我曾经想过要做的事，我决定将一个ID列添加到关系表(hospital_service)中，并将其用作service_pricing表中的外键.

What I have thought of doing, I have decided to add an ID column to the relationship table (hospital_service) and I will be using this as foreign key in service_pricing table.

就我而言，这是一个足够好的解决方案吗?我为此使用Laravel的Eloquent ORM，那么我该怎么说获取医院2的服务7的价格"?

Is this a good enough solution in my case? I am using Laravel's Eloquent ORM for this, so how do I say "get pricing of service 7 of hospital 2" ?

在service_pricing和service_duration表中添加了其他信息，价格/持续时间取决于weight_range_id以及其LH/SH.

Added additional information to service_pricing and service_duration table, the pricing / duration depends on weight_range_id and whether its LH / SH.

推荐答案

我应该在service_pricing表?

should I be rewriting the hospital_id vs service_id information in the service_pricing table?

如果需要，您可以.无论哪种方式，这只是最方便的问题.如果键是一个字段而不是两个字段，则与 service_pricing 和 service_duration 表连接起来要容易一些.

You can if you want to. Either way works, it's just a matter of what's most convenient. It's just a bit easier to join with the service_pricing and service_duration tables if the key is one field instead of two.

这不是重复数据吗?我已经有此信息了Hospital_service表.

Isn't this repetition of data? I already have this information in Hospital_service table.

不，它不被认为是多余的，因为它是连接记录的键值.您必须在外键中也具有该值才能完全建立连接.

No, it's not considered redundant, as it's the key values that connect the records. You have to have the value also in the foreign key to make a connection at all.

我为此使用Laravel的雄辩的ORM，所以我怎么说医院2的服务7的价格?

I am using Laravel's Eloquent ORM for this, so how do I say "get pricing of service 7 of hospital 2" ?

我不熟悉该框架，而laravel.com目前似乎已倒闭.的SQL将是这样的:

I'm not familiar with that framework, and laravel.com seems to be down at the moment. The SQL for that would be something like:

select
  weight_range_id, LH_price, SH_price
from
  service_pricing
where
  hospital_id = 2
  and service_id = 7

如果您决定在 hospital_service 表中添加另一个ID，则需要在该表中加入该查询:

If you decide to add another id in the hospital_service table, you would need to join in that table for that query:

select
  p.weight_range_id, p.LH_price, p.SH_price
from
  service_pricing p
  inner join hospital_service hs on hs.is = p.hospical_service_id
where
  hs.hospital_id = 2
  and hs.service_id = 7

这篇关于在其他表中使用关系表的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！