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

问题描述

我有两个使用关系表（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和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表中重写hospital_id和service_id信息吗？

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

如果你想的话。任何一种方式工作，这只是一个最方便的事情。如果键是一个字段而不是两个字段，那么只需要加入 service_pricing 和 service_duration / p>

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的Eloquent ORM如何说get
为医院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屋！