我有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目前似乎已倒闭.的SQL将是这样的:
I'm not familiar with that framework, and laravel 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
发布评论