My head hurts ::
Looking for some tips or guidance on how to implement the django models in order to achieve functional and logical design.
Tried a few different models, don’t want to go into it to deep and later spend more time on how to reverse it or find workaround on bad design.
# need user
# need manager
# need payroll
# need qa
# need shipping
# all this user type are employees
# don't want choicefield, static in the model
# want to be able to add additional department
# external user, basic privileges to view a few item but need to track activity (not public)
# or restrict temporally (job end)
class Employee(AbstractUser):
# basic user items inherited from django auth
# employee belongs to 1 Region
# employee can work in any city in the region
# employee have pay rate what change if working in different region
# employee have unique or static schedule
# employee report to 1 or more manager
# employe complete many jobs
# employee can work in other region without moving, reports to same manager(s)
# what relationship
class EmployeeType(models.Model):
type = models.CharField(max_length=64)
# manager, tech, payroll, qa, shipping, contractor
# each type with group privileges
# what relationship
class Department(models.Model):
dep = models.CharField(max_length=64)
# plumbing, electrical, mechanic, shipping
# what relationship
class Region(models.Model):
region = models.CharField(max_length=64)
# east, west
# region have many cities
# what relationship
class City(models.Model):
city = models.CharField(max_lentgh=64)
# Big Apple, Longhorn, Mountain View
# what relationship
class Rates(models.Model):
rate = models.IntegerField()
rate_effective_date = models.DateField()
# pay rate in east region + 10%
# $55 since hiring date, changed to $60 since 2022-04-01
# or 100% since hiring date, changed to %80 since 2022-04-01 (bad contractor)
# employee pay rate increased on april 1, previous contract jobs pays previous rate
# need some history model to track it
class Schedule(models.Model):
name = models.CharField(max_length=64)
# schedule on different model/app
# how set relationship
“not a working model, just typed up in the browser”
Doing a full data design on any complex environment is very much an iterative process. Probably the first thing you should be prepared for is that something along the line is going to need (or want) to be changed. But you still want to lay the best possible foundation at the beginning to minimize that.
We perform this type of data modelling using a two-layer approach. First, we identify the “entities” involved in the system, with absolutely no thought about tables or models. That discussion is purely focused the higher-level components and the business relationship between them.
For example, as a very superficial look at what you’ve posted here, you may have the entities “People”, “Department”, “Location”, and “Schedule”. The models you’ve listed that aren’t listed here may be part of one of these entities, or they may not. I don’t have enough knowledge about your application to say.
Once you’ve got the entities identified, then you can dive into the individual table structures. You’ll have identified the relationships between the entities at that point, which will help drive the structure of the individual models.
Doing this properly takes time and the involvement of multiple people - including the “customer”. It has always been beneficial in my experience to ensure there are multiple people involved. Questions get asked and assumptions get challenged - and the sooner those types of issues are identified, the easier it is to address them.
The last full modeling exercise I was involved with took three business days with 5 people. Typically, I plan on these taking a day. (The largest was a system conversion/migration. The data design meetings involved 12 - 15 people over two weeks. It was also the first such exercise I ever participated in.)
Yes it’s a bigger project than I anticipated at first since I had a previous version running for 2 years already, I taught I could use the structure and code snippet from it. I was so wrong 
How would you bind the cities to regions?
Add field to region to hold list of cities or a junction table to hold cities belonging to a region?
I wouldn’t know. I don’t have knowledge of the requirements to the detail necessary to offer any tangible suggestions.
I would go far enough to say:
I would not ever take this approach.
In the absence of sufficient knowledge of the specifics here, my general starting point would be to say that either there’s a ManyToMany relationship between City and Region, or City has a Foreign Key to Region.
Yes foreign key to region was my idea as well, unsure if on_delete=models.DO_NOTHING is good idea
If a region deleted the name of cities stay, when new region created need to link cities to it (unsure how to alter foreign key to new region 
From the perspective of “data integrity”, this is the worst of all possible options. (See the specific docs at DO_NOTHING)
You would want to either use SET_NULL or SET_DEFAULT, where the default would be a default region, such as one named “Unassigned”. Each one of those choices has their own pros and cons, but both facilitate identifying cities that aren’t currently assigned to a region.
How about .PROTECT nobody should delete a region unless it’s empty
That’s a “Business rule” decision. (It may or may not make sense for your application - I wouldn’t know.)