澄清数据库关系问题 [英] Clarify a db relationship problem
问题描述
大家好，
我在为桌子规范化或建立关系时遇到了麻烦。这就是我到目前为止所得到的：
tblPatients：
PatientPrimary＃（Number）[Pk]
PatientSecondary＃（正文）
tblPatientInfo：
PatientInfoID（自动）[Pk]
PatientEpisode（Number）
PatientPrimary＃（编号）[Fk]
tblChartInfo：
ChartID（自动）[Pk]
PatientEpisode [Fk]
ChartItem（文字）
- 我在tblPatients和tblPatientInfo之间的关系是PatientPrimary＃ - > PatientPrimary＃
- 我在tblPatientInfo和tblChartInfo之间的关系是PatientInfoID - > PatientEpisode
每位患者只能拥有剧集编号，但每集可能有多个ChartInfo项目。如果患者＃100在第1集中，则他们可能有10个图表项目用于该集。目前，如果我在tblPatientInfo上为患者输入多个相同的剧集，该表将接受它，但它不应该。
如果我将tblPatientInfo的主键更改为剧集编号，那么当我在tblChartInfo上输入一个值时，我没有一个剧集的多个项目而没有索引/重复错误信息出现。
我做错了什么？我附加了两个示例数据库...一个用于显示tblPatientInfo的不同主键。
*仅仅是为了记录，我在不同的论坛上发布了一个类似的，虽然不同的问题，但还没有得到回复，所以请不要惩罚我，如果你也读过那篇文章。
谢谢，
beacon
Hi everybody,
I''m having trouble normalizing or setting up relationships for my tables. Here''s what I''ve got so far:
tblPatients:
PatientPrimary# (Number) [Pk]
PatientSecondary# (Text)
tblPatientInfo:
PatientInfoID (Auto) [Pk]
PatientEpisode (Number)
PatientPrimary# (Number) [Fk]
tblChartInfo:
ChartID (Auto) [Pk]
PatientEpisode [Fk]
ChartItem (Text)
- My relationship between tblPatients and tblPatientInfo is PatientPrimary# -> PatientPrimary#
- My relationship between tblPatientInfo and tblChartInfo is PatientInfoID -> PatientEpisode
Each patient can only have on episode number, but there may be multiple items for ChartInfo per episode. If Patient #100 is on episode 1, they may have 10 chart items for that episode. As of right now, if I enter more than one of the same episode for a patient on the tblPatientInfo, the table will accept it, but it shouldn''t.
If I change the primary key of the tblPatientInfo to episode number, then when I enter a value on the tblChartInfo, I can''t have multiple items for one episode without have an index/duplicate errors message appear.
What am I doing wrong? I''ve attached two sample databases...one to show the different primary keys for the tblPatientInfo.
* Just for the record, I posted a similar, although different question on a different forum, but haven''t yet gotten a response, so please don''t chastise me if you also read that post.
Thanks,
beacon
推荐答案
Hello Beacon，
你能描述一下数据库是什么我的意思是这样做我可以理解你想要做什么。
虽然你提到它，你的2个样本没有表'tblChartInfo'。你也混淆了术语，所以我很难搞清楚它。
要确定关系类型（即：1：1,1：M或M：N）你需要一个每个链接表集最少2个业务规则（链接的每个方向有一个规则 - 这将决定您的关系类型）。一旦你知道关系类型，设计就很容易了。
因此，请给我一个关于你的数据库的解释，然后你想要完成什么，我会帮你解决。
欢呼，
Hello Beacon,
Could you describe what the database is meant to do so I can get an understanding of what you want done.
Your 2 samples don''t have a table ''tblChartInfo'' although you mention it. You are also mixing up terminology so I am having trouble figuring it out.
To determine relationship types (ie: 1:1, 1:M, or M:N) you need a minimum of 2 business rules per linked set of tables (one rule for each direction of the link - this will determine your relationship type). Once you know the relationship types the design is very easy.
Therefore give me an explanation of your database is used for and then what you want to accomplish and I will help you work it out.
cheers,
@beacon
正如你给我们的那样单挑我们会把它们视为不同的问题（如你所说）。
仅供参考：如果您需要跨论坛提问，可以发布链接在另一个不包含任何讨论的论坛中的线程，只是一个指向原始线程的链接。这样所有讨论都保持在一起。
@beacon
As you''ve given us the heads-up we''ll just treat them as different questions (as you say).
FYI: Should you need a question to be cross-forum, you can post a linking thread in another forum which won''t contain any discussion, simply a link to the original thread. That way all discussion is kept together.
我有两个问题需要理解你的解释：
I have two problems understanding your explanation :
- @beacon
是否可以允许多个tblPatientInfo记录，但它们必须共享相同的PatientEpisode值？ - @beacon
这看起来很奇怪，特别要注意你有一个tblChartInfo.PatientEpisode字段。
@beacon
这听起来像你有在tblChartInfo.Pa上指定了索引tientEpisode一样独特。这不正确。
@beacon
This sounds like you have specified the index on tblChartInfo.PatientEpisode as unique. This would not be correct.
这篇关于澄清数据库关系问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
