Design Problem

Hi guys,

I am finishing my CS course, and I decided to use Django to implement EPOS + Employee Scheduling + Stock Management system into one web app for my final year project.

I have started building the application, but I am at a point where I am not too sure what is the best way to approach the design of a specific model.

The web app that I am trying to build will need to use a calendar date in some way.
I need to store daily, weekly, monthly, and yearly reports; employees worked on a specific day, total cost, etc. For instance:

  • each day needs to store
  1. Employees worked that day + their working hours
  2. End-of the day financial report report
  3. Received raw goods
  4. Costs
  5. Profit

I was thinking of building 4 Models:

  • Date
  • Day
  • Month
  • Year

Where Date will include the other three and each day at 00:01 on midnight, a new Date object would be automatically generated.

I am not looking for an exact answer on how to do it; any advice or example would be appreciated.

I would just include a DateField in each of your models, I don’t see much value in making a Date a separate model. (I’d also create an index on that field to ensure that searching for entries by date is as fast as possible.) Also, Day / Month / Year can be derived from the generic Date field and doesn’t need to be stored separately.

I came up with the following model architecture. Would appreciate any feedback.

Thanks in advance!

Feedback - some general thoughts, given with the understanding that I am not familiar with the processing that needs to occur. These entities don’t exist in a vacuum - they exist only to support some type of data management needs. Those needs can and should greatly affect the design of the models.

So, working only from general principles while acknowledging my lack of knowledge:

  • There is no need for a Date table requiring other tables to join to it to identify a date. If there are special dates that need some sort of extra processing, the typical method is to have a “Calendar” object that can be referenced when needed as opposed to needing to access it for every date reference. (As one specific example - in your Ingredient table you have an M2M for the date_purchased and expiry_date - does it really matter to the date_purchased if it was a weekend or a holiday?)
  • Never, under any condition or circumstance, for absolutely no reason whatever, should you ever, ever even think about using FloatField for financial or financial-related fields. Not for costs, prices, totals, hours, rates, nothing - just don’t do it. Use the Decimal field for them.
  • You seem to have some redundancy in the Employee model between is_employed, is_employee, is_supervisor, is_manager. I’d be curious to understand the purpose of each of those with an eye toward determining if any are redundant or mutually exclusive to any of the others.
  • Schedule: scheduled_cost and actual_cost are derived fields. I wouldn’t store them in the table
  • Product: You’re not consistent with your boolean fields between tables. You established the idea of is_… in Employee but abandoned it here.
  • Ingredient: Naming conventions - date_purchased vs expiry_date. (Should perhaps be either purchase_date and expiry_date, or date_purchased and date_expires.)
  • Report: Again here you’re storing derived values when there’s no apparent need to do so. Also, are most_sold_product and most_products_sold_by FKs to Product and Employee?
  • TimeStamp: Are these “Sign in” and “Sign out” events for the website, or are they a reference to starting and stopping work?
  • Supplier: productType - inconsistent use of camelCase instead of underscore

So these are just a few thoughts on what jumped out at me while I was looking at this. Hope they give you some ideas for further refinements.

Hi Ken,

Thanks a lot for the feedback!

Sorry for not adding requirements, the list is quite long, but here are some of the ones I am unsure on how to model:

Sales analytics and Report generation Daily/Weekly/Monthly/Yearly
Employee scheduling for each day. On opening the schedule part of the web app, there would be a page with today’s date. A list of all working employees would be displayed for that day, with their:

  • pay rate
  • total scheduled hours by a manager for that day
  • the time at which the employee signed in
  • hours worked so far since signed in
  • scheduled finishing time
  • Earned salary so far (for the day only)
  • The time an employee signed out

Employee data and sales data must be stored for each day. It would be used for analytic graphs and financial reports. Also, I am planning on implementing a module that looks over the previous sales patterns. For instance, it checks how many chocolates have been sold for the last 4 weeks and derives an average sold chocolate per week, based on that, it decides how many chocolates to order for the following week and it makes an order to the suppliers automatically. So orders (customer orders) and their dates would also be used from the Stock Management functionality.

My idea over the Date model was to have it for the scheduling functionality. I have done some research and I actually might just use a DateField.

  • The 3 boolean fields in the Employee model would be used for different application access. Managers would have access to all settings and functionalities, whereas, supervisors would have some restrict access. Employees would have no access to scheduling other employees, generating reports or check financial analytics. The idea is, an employee might be up for promotion, but still be an employee in the system, however, he needs a way to access extra functionalities from the ones that are only for employees. Only managers would be allowed to set those values.
  • Do I implement the derived fields as a property method (with property decorator)?
  • I will fix boolean fields between tables and the naming conventions.
  • Yes, most_sold_poduct and most_products_sold_by would be an FK to Product and Employee
  • TimeStamp would be signed in and sign out for the day. Once an employee starts its shift would have to sign in in order to have access to the EPOS.

Regards

Addressing individual points as I can:

On the surface, this appears to be more of a “process” issue than a data issue. Am I missing something here?

This may be just a terminology issue, but I would never rely upon the act of physically signing in / out to a web app for any management purposes. To avoid confusion and other problems, I’d name them something like shift_start and shift_end or clock_in and clock_out or anything which makes sense to you other than the generic terms sign in and sign out, and give the user a button to save the current time stamp in the appropriate field(s).
(While physically signing in to a web app is an easy-to-track event, this is not true and never guaranteed for a “sign out” event.)

This is the purpose of the Group model and the built-in Permission system. Make the employees members of the appropriate groups.

You can if you wish. Me? I wouldn’t bother with the property decorator unless I found a situation where it was required. My first shot would be to just create it as a model method. (After all, it is read-only - you’re not going to be writing to it.)

Ken