我有一张机器数据表:
machines
----------
id
owner_id
// other data
我们还有来自德国和法国的官方机器数据。两者具有相同的信息:
# Machine data (for german and french machines)
- machine_id: to tie to machines table
- record_number: unique number for each machine, different format for each country
- machine_name: the name of the machine
- market_id: id associated with the machine market, different format for each country
# Market data (also for german and french machines)
- market_id: machine market id, different format for each country
- market_name: name of the market
- specific country information that is different for France and Germany
起初我认为关系模式如下所示:
machines
---------
machine_id
owner_id
// other data
machine_information
---------
machine_id
record_number
country (German or France)
machine_name
market_id
france_markets
---------
id
market_name
// France specific column data
german_markets
---------
id
market_name
// German specific column data
但这会使过滤数据变得非常困难,因为在获取机器信息时,我们不知道我们必须加入哪个市场表。
我还想将 machines_information 分成 2 个,每个国家一个
machines
---------
machine_id
owner_id
// other data
france_machine_information
---------
machine_id
record_number
machine_name
market_id
german_machine_information
---------
machine_id
record_number
machine_name
market_id
france_market
----------
id
market_name
// France specific column data
german_market
----------
id
market_name
// German specific column data
但是这样也有同样的问题,因为在获取机器信息的时候,我们并不知道要加入哪个机器信息表。
这种设计有好的解决方案吗?
谢谢你!