[Fixed]-Database design for online food delivery system with book a table

1đź‘Ť

âś…

As stated in my comment, this is an opinion based question and there can be many different answers. Also this question doesn’t really suit a site like stackoverflow.

But let’s try to help you.

Customer
    user(FK) # why do you need this?

If you want to extend the django User class, this wouldn’t really do the job. You’ll need 1:1 relation between Customer and User. Also in your other models you should reference further to Customer, and not to User.
So make it rather:

Customer(models.Model):
    user = models.OneToOneField('User', ...)

In the entity Restaurant (and all other entities) reference to Customer. I’m wondering why do you have User:Restaurant 1:n relation. Anyway you should change that to:

Restaurant(models.Model):
    customer = models.ForeignKey('Customer', ...)

If you can, use PostgreSQL, and the JSON field for the opening times. Then you can have something like this:

    timings = JSONField()

and the JSON could look like:

{
    'Monday' : {
        'opens': '10am',
        'closes': '10pm'
    },
    'Tuesday' : {
        'dayoff': true
    },
    'Wednesday': {
        'opens': '9am',
        'closes': '11pm'
    }
    # and so on
}

You can (and should) put the time in different format, but this is just an illustration. The JSON can be very flexible, you can leave out days, you can have different properties per day, include break maybe (a restaurant opens at 9am until 2pm and then has break to 5pm, when it opens again until 10pm). So the properties opening_time, closing_time and timings should be removed i.e. merged in this JSON field.

Make the status a virtual field. Write a method that gets the current time and check if the restaurant is open or closed. Don’t persist this into the database.

Your entity Menu should be rather called Meal or MenuItem. Think better how you name your variables. Naming is very important, I’d say the most important in programming. If you can’t properly name a class, method or property, you don’t really know what it is.

In your Order you have food_id(M2M) and restaurant(FK). Why? If food_id(M2M) goes to the previous model, you have the restaurant there. Unless you want to have something like let’s say “Beef burger”, “Chicken gumbo”, and that can be served by many different restaurants.
And why is quantity a property of Order? When I think of order, it could look like this:

- 3 beers
- 1 coke
- 2 buffallo wings
- 1 spare ribs
- 1 nachos

So which quantity do you mean? I’d do a M2M table with extra fields, in Django you can specify through table and define that model, too, with the needed additional fields:

OrderFood(models.Model):
    order_id(FK)
    food_id(FK)
    quantity

I could write even more, but that’s for now. I hope it helps you a little bit.
Put everything down on paper. Draw your entities – not writing the names – make a sketch. Draw the customer, the restaurant, the table, the food and assign them the properties and think in which relation they stand.

EDIT:

check the documentation for the JSONField:
https://docs.djangoproject.com/en/1.11/ref/contrib/postgres/fields/#jsonfield

I’d make the skeleton as standard relational database and put all flexible properties in JSON. Think of the menu as very different depending on the restaurant. A fast food take away can have:

{
    'Burgers': [
        'Hamburger',
        'Cheesburger'
    ],
    'Beverages': [
        'Coke',
        'Fanta'
    ]
}

and a noble French restaurant can have:

{
    'Entrees': [
        ... whatever
    ],
    'Main courses': [
         {'Poultry': [ ... ]},
         {'Beef': [ ... ]},
         {'Fish': [ ... ]}
    ],
    'Deserts': [
        ... whatever
    ],
    'Beverages': [
        {'Wines': [
            {'White': [...]},
            {'Red': [...]}
        },
        {'Aperitifs': [...]},
        {'Beers': [...]}
    ]
}

EDIT: explain virtual model field

Virtual model field means the model entity has a property which is not persisted to the database and as such doesn’t exist as a column in the database table.

Example:

import calendar
from datetime import datetime

Restaurant(models.Model):
    timings = JSONField()

    # illustrational code, not for production
    def _get_status(self):
        now = datetime.now()
        weekday = calendar.day_name[now.weekday()]
        if self.timings.get(weekday, False):
             open = self.timings[weekday].get('opens', 0)
             close = self.timings[weekday].get('closes', 0)
             if now.hour() >= open && now.hour() < close:
                 status = 'open'
             else:
                 status = 'closed'
        else:
             status = 'closed'
        return status

    status = property(_get_status)
    status.short_description('Status open-closed')

For status there won’t be a column in the table. You don’t save that value in the database, as it depends on the current moment. Therefore you check if the restaurant is open right now. You can get the current weekday and the current hour and compare that with the values in the JSONField.

👤cezar

Leave a comment