# Design Problem **URL:** \ **Category:** Using Django\ **Created:** [December 1, 2020, 7:42pm UTC](https://forum.djangoproject.com/t/design-problem/5490 "2020-12-01T19:42:12Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![antpngl92](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/antpngl92/32/1035_2.png) [@antpngl92](https://forum.djangoproject.com/u/antpngl92)\ **Post date:** [December 1, 2020, 7:42pm UTC](https://forum.djangoproject.com/t/design-problem/5490/1 "2020-12-01T19:42:12Z")
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. ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [December 1, 2020, 8:04pm UTC](https://forum.djangoproject.com/t/design-problem/5490/2 "2020-12-01T20:04:17Z")
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. ---
**Author:** ![antpngl92](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/antpngl92/32/1035_2.png) [@antpngl92](https://forum.djangoproject.com/u/antpngl92)\ **Post date:** [December 2, 2020, 1:05am UTC](https://forum.djangoproject.com/t/design-problem/5490/3 "2020-12-02T01:05:08Z")
I came up with the following model architecture. Would appreciate any feedback. ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/2X/a/a112f8db26a41d1a8d08e64343604ae5e3f3115c.png) Thanks in advance! ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [December 2, 2020, 2:01am UTC](https://forum.djangoproject.com/t/design-problem/5490/4 "2020-12-02T02:01:08Z")
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. ---
**Author:** ![antpngl92](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/antpngl92/32/1035_2.png) [@antpngl92](https://forum.djangoproject.com/u/antpngl92)\ **Post date:** [December 2, 2020, 4:39am UTC](https://forum.djangoproject.com/t/design-problem/5490/5 "2020-12-02T04:39:56Z")
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 ---
**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\ **Post date:** [December 3, 2020, 3:56pm UTC](https://forum.djangoproject.com/t/design-problem/5490/6 "2020-12-03T15:56:28Z")
Addressing individual points as I can: > [@antpngl92](#): > > Sales analytics and Report generation Daily/Weekly/Monthly/Yearly On the surface, this appears to be more of a “process” issue than a data issue. Am I missing something here? > [@antpngl92](#): > > - the time at which the employee signed in > - The time an employee signed out 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.) > [@antpngl92](#): > > The 3 boolean fields in the Employee model would be used for different application access. This is the purpose of the Group model and the built-in Permission system. Make the employees members of the appropriate groups. > [@antpngl92](#): > > Do I implement the derived fields as a property method (with property decorator)? 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