CODEWITHSUNDEEP
djangopostgresqldjango-modelsmany-to-many
J. Hesters
J. Hesters

Reputation: 14786

Postgresql + Django: What is better between same strings or a many to many relationship?

I'm currently designing my data base using postgresql with Django and I was wondering: What is best practice - having several instances of the same model with the same value or a many to many relation ship?

Let me elaborate. Let's say I'm designing a store. The store sells items. Items can have one or many statuses (e.g. ordered, shipped, delivered, paid, pre-ordered etc.).

What would be a better practice:

Relating the items to their status via a many-to-many relationship, which will lead to one status having hundreds of thousand and later millions of relations? Will so many relations become problematic?

Or is it better for each item to have a foreignkey to their statuses? So that each status only has one item. And if I would like to query all the items that have the same status (e.g. shipped), I would have to iterate over all statuses with a common name.

What would be better, especially for the long term?

Upvotes: 0

Views: 75

Answers (2)

Diego Vinícius
Diego Vinícius

Reputation: 2223

Let me elaborate. Let's say I'm designing a store. The store sells items. Items can have one or many statuses (e.g. ordered, shipped, delivered, paid, pre-ordered etc.).

If many people will have this many itens you should use manytomany relations, better let django handle with this "third table", since this table just hold ids you can interate over them using reverse lookup, i do prefer using many to many instad of simple foreignkeys.

In your case, who you will handle when your User will hold many itens? like what if my User buy one potato and 2 bananas? you will duplicate the tuple in your User Table to tell "here he have the potato and in this second one he have the banana"? so you will be slave of Disctinct attribute while you still dirtying your main table User

...
class Item(models.Model):
    ...

class User(models.Model):
    items = models.ManyToMany(Item)

So when i query my Item and my User will only bring attributes related to them... while if you use item inside of User Model you will have multiple instances of same user.

So instead of use User.items.all() you will use User.objects.filter(id=id)and them items = [user.item for user in User.objects.filter(id=id)]

Look how complex this get and makeing your database so dirty

Upvotes: 0

Brian Guggenheimer
Brian Guggenheimer

Reputation: 233

I would recommend going with a many-to-many relationship.

Hundreds of thousands or even millions of relations should not be a problem. The many-to-many relationship is stored as a table with id, item_id, status_id. SQL will be performant at querying the table either by status_id or item_id even if the table gets big. This is exactly the kind of thing it was built to handle.

Upvotes: 1

Related Questions