How to model lending items between a group of companies

I have a group of related companies that share items they own with one-another. Each item has a company that owns it and a company that has possession of it. Obvious开发者_JAVA百科ly, the company that owns the item can also have possession of it. Also, companies sometimes permanently transfer ownership of items instead of just lending it, so I have to allow for that as well.

I'm trying to decide how to model ownership and possession of the items. I have a Company table and an Item table.

Here are the options as I see them:

1. Inventory table with entries for each Item - Company relationship. Has a company field pointing to a Company and has Boolean fields is_owner and has_possession.
2. Inventory table with entries for each Item. Has an owner_company field and a possessing_company field that each point to a Company.
3. Two separate tables: ItemOwner and ItemHolder**.

So far I'm leaning towards option three, but the tables are so similar it feels like duplication. Option two would have only one row per item (cleaner than option one in this regard), but having two fields on one table that both reference the Company table doesn't smell right (and it's messy to draw in an ER diagram!).

Database design is not my specialty (I've mostly used non-relational databases), so I don't know what the best practice would be in this situation. Additionally, I'm brand new to Python and Django, so there might be an obvious idiom or pattern I'm missing out on.

What is the best way to model this without Company and Item being polluted by knowledge of ownership and possession? Or am I missing the point by wanting to keep my models so segregated? What is the Pythonic way?

Update

I've realized I'm focusing too much on database design. Would it be wise to just write good OO code and let Django's ORM do it's thing?

Is there a reason why you don't want your item to contain the relationship information? It feels like the owner and possessor are attributes of the item.

class Company(models.Model):
    pass

class Item(models.Model):
    ...
    owner = models.ForeignKey(Company, related_name='owned_items')
    holder = models.ForeignKey(Company, related_name='held_items')

Some examples:

company_a = Company.objects.get(pk=1)

company_a.owned_items.all()
company_a.held_items.all()

items_owned_and_held_by_a=Items.objects.filter(owner=company_a, holder=company_a)

items_on_loan_by_a=Items.objects.filter(owner=company_a).exclude(holder=company_a)
#or
items_on_loan_by_a=company_a.owned_items.exclude(holder=company_a)

items_a_is_borrowing=Items.objects.exclude(owner=company_a).filter(holder=company_a)
#or
items_a_is_borrowing=company_a.held_items.exclude(owner=company_a)

company_b = Company.objects.get(pk=2)

items_owned_by_a_held_by_b=Items.objects.filter(owner=company_a, holder=company_b)
#or
items_owned_by_a_held_by_b=company_a.owned_items.filter(holder=company_b)
#or
items_owned_by_a_held_by_b=company_b.held_items.filter(owner=company_a)

I think if your items are only owned by a single company and held by a single company, a separate table shouldn't be needed. If the items can have multiple ownership or multiple holders, a m2m table through an inventory table would make more sense.

class Inventory(models.Model):
    REL = (('O','Owns'),('P','Possesses'))

    item = models.ForeignKey(Item)
    company = models.ForeignKey(Company)
    relation = models.CharField(max_length=1,choices=REL)

Could be one implementation, instead of using booleans. So I'd go for the first. This could even serve as an intermediate table if you ever decide to use a 'through' to relate items to company like this:

Company:
    items = models.ManyToManyField(Item, through=Inventory)

Option #1 is probably the cleanest choice. An Item has only one owner company and is possessed by only one possessing company.

Put two FK to Company in Item, and remember to explicitly define the related_name of the two inverses to be different each other.

As you want to avoid touching the Item model, either add the FKs from outside, like in field.contribute_to_class(), or put a new model with a one-to-one rel to Item, plus the foreign keys.

The second method is easier to implement but the first will be more natural to use once implemented.