CODEWITHSUNDEEP
pythondatabase-designsqlalchemy
Ominus
Ominus

Reputation: 5721

sqlalchemy / table setup

I have items, warehouses, and items are in warehouses.

So I have table that has information about items (sku, description, cost ...) and a table that describes warehouses(location, code, name, ...). Now I need a way to store inventory so that I know I have X items in warehouse Y. An item can be in any warehouse.

How would I go about setting up the relationship between them and storing the qty? 

class Item(DeclarativeBase):
    __tablename__ = 'items'
    item_id = Column(Integer, primary_key=True,autoincrement=True)
    item_code = Column(Unicode(35),unique=True)
    item_description = Column(Unicode(100))
    item_long_description = Column(Unicode())
    item_cost = Column(Numeric(precision=13,scale=4))
    item_list = Column(Numeric(precision=13,scale=2))
    def __init__(self,code,description,cost,list):
        self.item_code = code
        self.item_description = description
        self.item_cost = cost
        self.item_list = list

class Warehouse(DeclarativeBase):
    __tablename__ = 'warehouses'
    warehouse_id = Column(Integer, primary_key=True, autoincrement=True)
    warehouse_code = Column(Unicode(15),unique=True)
    warehouse_description = Column(Unicode(55))

If I am correct I would setup the many to many using an intermediate table something like ...

item_warehouse = Table(
    'item_warehouse', Base.metadata,
    Column('item_id', Integer, ForeignKey('items.item_id')),
    Column('warehouse_id', Integar, ForeignKey('warehouses.warehouse_id'))
)

But i would need to start the qty available on this table but since its not its own class I am not sure how that would work.

What would be the "best" practice for modeling this and having it usable in my app?

Upvotes: 0

Views: 432

Answers (2)

van
van

Reputation: 77012

Model:

As mentioned by @Lafada, you need an Association Object. As such I would create a SA-persistent object and not only a table:

class ItemWarehouse(Base):
    # version-1:
    __tablename__ = 'item_warehouse'
    __table_args__ = (PrimaryKeyConstraint('item_id', 'warehouse_id', name='ItemWarehouse_PK'),)
    # version-2:
    #__table_args__ = (UniqueConstraint('item_id', 'warehouse_id', name='ItemWarehouse_PK'),)
    #id = Column(Integer, primary_key=True, autoincrement=True)

    # other columns
    item_id = Column(Integer, ForeignKey('items.id'), nullable=False)
    warehouse_id = Column(Integer, ForeignKey('warehouses.id'), nullable=False)
    quantity = Column(Integer, default=0)

This covers the model requirement with the following:

  • added a PrimaryKey
  • added a UniqueConstraint covering the (item_id, warehouse_id) pairs.

In the code above this is solved in two ways:

  • version-1: uses composite primary key (which must be unique)
  • version-2: uses simple primary key, but also adds an explicit unique constraint [I personally prefer this option]

Relationship: Association Object

Now. You can use the Association Object as is, which will look similar to this:

w = Warehouse(...)
i = Item(name="kindle", price=...)
iw = ItemWarehouse(quantity=50)
iw.item = i
w.items.append(i)

Relationship: Association Proxy extension

or, you could go one step further and use the Composite Association Proxies example, and you may configure dictionary-like access to the association object similar to this:

w = Warehouse(...)
i = Item(name="kindle", price=...)
w[i] = 50 # sets the quantity to 50 of item _i_ in warehouse _w_
i[w] = 50 # same as above, if you configure it symmetrically

Beware: the code for the relationships definition might look really not easily readable, but the usage pattern is really nice. So if this option is too much to digest, I would start with Association Object with maybe some helper functions to add/get/update the item stocks, and eventually move to the Association Proxy extesion.

Upvotes: 2

Neel
Neel

Reputation: 21297

You have to use "Association Object".

I try to give you hint for your problem you have to create table like you mention in your question 

item_warehouse = Table( 'item_warehouse', 
                        Base.metadata, 
                        Column('item_id', 
                               Integer, 
                               ForeignKey('items.item_id')
                        ), 
                        Column('warehouse_id', 
                               Integar, 
                               ForeignKey('warehouses.warehouse_id')
                        ), 
                        Column('qty',
                               Integer,
                               default=0,
                        ),
                      )

Now you can add warehouse, item and qty in single object and you have to write method which will take warehouse_id and item_id and get the sum of qty for those itmes.

Hope this will help you to solve your problem.

Upvotes: 2

Related Questions