First of all, I'm sorry about the feedback-nature of this question. I'm trying to generalize it as much as I can so others can gain from it as well, but I don't really have anyone to give me feedback on this design, so I hoped you guys could help me.
首先,我很抱歉这个问题的反馈性质。我试图尽可能多地概括它,以便其他人也可以从中获益,但我真的没有人给我反馈这个设计,所以我希望你们能帮助我。
With that being said, I wan't to model different usertypes in my database. I would like to have the credentials for the usertypes to be shared though. This problem is basically about inheritance, which is something that the RDB's doesn't do too well.
话虽如此,我不想在我的数据库中建模不同的usertypes。我想要共享usertypes的凭据。这个问题基本上是关于继承,这是RDB不能做得太好的事情。
I did however come up with this design: DB design http://img48.imageshack.us/img48/9196/dbdesign.png
然而,我确实提出了这个设计:数据库设计http://img48.imageshack.us/img48/9196/dbdesign.png
.. but I'm unsure if I should be satisfied with it. What I don't like about it is the amount of business-contracts in it. First of all, I don't know which usertype belongs to a given credential, which means that I potentially have to search N tables, with N being the number of usertypes I got. Therefore, I thought of linking the type of a user with the roles he or she is in. So, if a user with credentials A has the roles of "UserType1" and "UserType2" I would expect to have a tuggle in the UserType1 and UserType2 tables representing him or her. - and I'm not sure if I like these "business-logic"-constraints.. :)
..但我不确定我是否应该满意。我不喜欢它的是商业合同的数量。首先,我不知道哪个usertype属于给定的凭证,这意味着我可能必须搜索N个表,其中N是我得到的usertype的数量。因此,我想到了将用户的类型与他或她所处的角色相关联。因此,如果具有凭据A的用户具有“UserType1”和“UserType2”的角色,我希望在UserType1和UserType2表代表他或她。 - 我不确定我是否喜欢这些“商业逻辑” - 约束...... :)
Any feedback on this given design would be very much appreciated, just as any alternative designs would be.
对于这个给定设计的任何反馈都将非常感激,就像任何替代设计一样。
Thanks in advance
提前致谢
1 个解决方案
#1
You can put a discriminator column in the parent if it's analogous to an abstract class in OO. The discriminator has a different value for each child type. If you're clever, it's an fk to a table with one row per child type.
如果它与OO中的抽象类类似,则可以在父类中放置一个鉴别器列。鉴别器对于每种子类型具有不同的值。如果你很聪明,这是一个表格,每个孩子类型一行。
Or you can just rely on that the join of authcredentials to usertype1 succeeds for any usertype1, but for no other child type, and similarly for the other tables. On a left outer join to each child table, all columns (but particularly the id) of the type it isn't are null, and the id it is isn't null. You can then add a calculated column based on this:
或者您可以依赖于authcredentials与usertype1的连接对任何usertype1成功,但对于其他子类型没有成功,对于其他表也是如此。在每个子表的左外连接上,它不是的类型的所有列(但特别是id)都是null,并且它的id不是null。然后,您可以根据此添加计算列:
select
a.*, b.*, c.*,
case when b.id is not null then 1 else 0 end as is_usertype1,
case when c.id is not null then 1 else 0 end as is_usertype2,
from authcredentials a
left outer join usertype1 b on (a.id = b.authcredential_id )
left outer join usertype2 c on (a.id = c.authcredential_id );
Then make that select a view for ease of use. Inserts will still be to the separate table usertype and usertype2, but then in OO programming, ctors aren't inherited either and two subclasses of a common based don't necessarily have similar ctors.
然后选择一个视图以方便使用。插入仍然是单独的表usertype和usertype2,但是在OO编程中,ctors也不是继承的,并且基于公共的两个子类不一定具有相似的ctors。
Just as in C++, where the base class is constructed before the derived class, you'll have to create the parent row bfore you can create a child row (you need the FK).
就像在C ++中,在派生类之前构造基类一样,您必须在创建子行之前创建父行(您需要FK)。
postgresql explicitly supports table inheritance just like this. Hibernate ORM supports it for mapping tables to Java subclasses.
postgresql显式支持表继承,就像这样。 Hibernate ORM支持将表映射到Java子类。
#1
You can put a discriminator column in the parent if it's analogous to an abstract class in OO. The discriminator has a different value for each child type. If you're clever, it's an fk to a table with one row per child type.
如果它与OO中的抽象类类似,则可以在父类中放置一个鉴别器列。鉴别器对于每种子类型具有不同的值。如果你很聪明,这是一个表格,每个孩子类型一行。
Or you can just rely on that the join of authcredentials to usertype1 succeeds for any usertype1, but for no other child type, and similarly for the other tables. On a left outer join to each child table, all columns (but particularly the id) of the type it isn't are null, and the id it is isn't null. You can then add a calculated column based on this:
或者您可以依赖于authcredentials与usertype1的连接对任何usertype1成功,但对于其他子类型没有成功,对于其他表也是如此。在每个子表的左外连接上,它不是的类型的所有列(但特别是id)都是null,并且它的id不是null。然后,您可以根据此添加计算列:
select
a.*, b.*, c.*,
case when b.id is not null then 1 else 0 end as is_usertype1,
case when c.id is not null then 1 else 0 end as is_usertype2,
from authcredentials a
left outer join usertype1 b on (a.id = b.authcredential_id )
left outer join usertype2 c on (a.id = c.authcredential_id );
Then make that select a view for ease of use. Inserts will still be to the separate table usertype and usertype2, but then in OO programming, ctors aren't inherited either and two subclasses of a common based don't necessarily have similar ctors.
然后选择一个视图以方便使用。插入仍然是单独的表usertype和usertype2,但是在OO编程中,ctors也不是继承的,并且基于公共的两个子类不一定具有相似的ctors。
Just as in C++, where the base class is constructed before the derived class, you'll have to create the parent row bfore you can create a child row (you need the FK).
就像在C ++中,在派生类之前构造基类一样,您必须在创建子行之前创建父行(您需要FK)。
postgresql explicitly supports table inheritance just like this. Hibernate ORM supports it for mapping tables to Java subclasses.
postgresql显式支持表继承,就像这样。 Hibernate ORM支持将表映射到Java子类。