你会如何设计这个数据库？个人和团体所有权

I have a list of items, and items can be owned either by an individual person or a group of people.

我有一个项目列表，项目可以由个人或一群人拥有。

So far I have 3 tables like:

到目前为止，我有3个表，如：

People:

人：

personID  (primarykey)
personName
personAddress
...

Groups:

团体：

groupID  (primarykey)
groupNumber
personID

Items:

项目：

itemID  (primarykey)
itemName
itemDescription
itemWeight
...

How would you go about assigning ownership of items to an individual, or a specific group?

您如何将项目的所有权分配给个人或特定群体？

I did have 2 fields on the item, owner_personID and owner_groupID, but seems kinda janky, and have to manually manage which field to use at a time.

我确实在项目上有2个字段，owner_personID和owner_groupID，但看起来有点混乱，并且必须手动管理一次使用哪个字段。

I also tried two linking tables, PeopleOwnerships, and GroupOwnerships, that store a list of items owned. With fields like, personID/groupID and itemID. But, seems even more jankier and can get messy. Have to be extra careful so that both individuals and groups don't own the same item at the same time.

我还尝试了两个链接表，PeopleOwnerships和GroupOwnerships，它们存储了所拥有的项目列表。使用personID / groupID和itemID等字段。但是，似乎更加笨拙，可能会变得混乱。必须格外小心，以便个人和团体不会同时拥有相同的物品。

I have also thought about putting all individuals in their own individual group, and just using the Groups table to refer to everything. But that also seems kind of weird.

我还想过将所有个人都放在他们自己的团体中，并且只使用Groups表来引用所有内容。但这似乎有点奇怪。

I would also need to record the transfer of items, between people and groups, when ownership changes. I've thought about having a date field in the linking tables, and using the highest date to determine who owns what.

当所有权发生变化时，我还需要记录人员和群组之间的项目转移。我想过在链接表中有一个日期字段，并使用最高日期来确定谁拥有什么。

1 个解决方案

#1

You could treat a person as a group of 1 person, and only allow groups to own items.

您可以将一个人视为一个人的一组，并且只允许组拥有项目。

Something better may be to have a class called "Owner" which only has the ID of the item and its ID (the id of the owner), and get both classes Person and Group extending it.

更好的方法可能是拥有一个名为“Owner”的类，它只有项目的ID和ID（所有者的id），并获得Person和Group扩展它的类。

About the transfers, I wouldn't rely that business logic on the database.

关于传输，我不会依赖数据库上的业务逻辑。

#1