I'm writing a Flask API which resembles an online shopping website like Amazon or eBay and it's using SQLAlchemy as an ORM for my database. One of the main features is posting a product for sale, which another user can order. Every added product will be registered in a 'Product' table and once an order is made, it is going to be registered in the 'Order' table which also includes foreign key with product's ID.
Initially I wanted the 'Product' table to have a 'Sold' column (boolean) which is False by default and changed to True when an order for a product is made. However, because it is a relational database I'm wondering if it makes sense to include this column since SQLAlchemy allows for creating relationships between tables.
This is what currently (simplified) SQLAlchemy models of these two tables look like:
class Product(db.Model):
__tablename__ = 'product'
id = db.Column(db.Integer, primary_key=True)
name = db.Column(db.String(200))
price = db.Column(db.Float)
category = db.Column(db.String(200))
sold = db.Column(db.Boolean, default=False)
order = db.relationship('Order', backref='product', uselist=False)
class Order(db.Model):
__tablename__ = 'order'
id = db.Column(db.Integer, primary_key=True)
shipment_method = db.Column(db.String(200))
product_id = db.Column(db.Integer, db.ForeignKey('product.id'), unique=True, nullable=False)
Therefore if a user sends a request to the API to return a list of products for sale, they can filter them by passing 'sold=False' parameter to the route responsible for listing and filtering all products.
On the other hand, if the 'Sold' column was to be removed I could make use of SQLALchemy relationships and find not-sold products by checking if 'product.order == None'. However, there would be a need to create a special route which returns a list of products for sale since passing 'sold=False' parameter by user would no longer work.
@products.route('/for-sale', methods=['GET'])
def query_products_for_sale():
products = Product.query.all()
products_for_sale = []
for product in products:
if product.order == None:
temp_dict = columns_to_dict(product)
products_for_sale.append(temp_dict)
return jsonify(products=products_for_sale)
I know there is probably an easier way of writing the above code but I just wanted to show that it can be done without having a 'Sold' column. That's why I wanted to ask: what is the best solution in this case? Or best practice in general when it comes to this type of relational database issues?
