Creating seperate unique ids within the same table

[Joel Mathew]

I have an application which is a clinic management software which allows login into different clinics, and needs to keep patient records of each clinic seperately. The patient records are filed in one common model, with a ForeignKey referring to the clinic to which a patient belongs.

Hitherto I had used the AutoField primary key of the customer model as a hospital identification number for the patient. This number is unique and used throughout the application to retrieve querysets related to the customer. My problem is this. I want each patient to have a unique automatically incrementing id which can be given to them. Obviously since all patient records (irrespective of which clinic the patient belongs to) are stored in one model, the ids are not seperated by clinic. cstid 1 may be of clinic 1, cstid 5 may again be of clinic 1, but cstid 6 may be of clinic 2. 

I need patients  of one clinic to have continuous ids unique to each clinic. What can I do to achieve this, within my defined models?

    class Clinic(models.Model):

        clinicid = models.AutoField(primary_key=True, unique=True)

        name = models.CharField(max_length=60, unique=True)

        label = models.SlugField(max_length=25, unique=True)

        email = models.EmailField(max_length=50, default='')

        mobile = models.CharField(max_length=15, default='')

        alternate = models.CharField(max_length=15, default='', blank=True)

        about = models.CharField(max_length=250, blank=True)

        state = models.CharField(max_length=25)

        city = models.CharField(max_length=35)

        locality = models.CharField(max_length=35)

        pincode = models.IntegerField(default=0)

        address = models.TextField(max_length=80, default='', blank=True)

        website = models.URLField(blank=True)

        logo = models.ForeignKey(ProfilePic, blank=True, null=True, on_delete=models.CASCADE)

        latitude = models.FloatField(blank=True)

        longitude = models.FloatField(blank=True)

        placeurl = models.URLField(blank=True)

        class Meta:

            unique_together = ["name", "mobile", "email"]

        def __str__(self):

            return self.name

            

    class customer(models.Model):

        # Need autoincrement, unique and primary

        cstid = models.AutoField(primary_key=True, unique=True)

        name = models.CharField(max_length=35)

        age=models.IntegerField()

        gender_choices = (

            ('male', 'Male'),

            ('female', 'Female'),

            ('other', 'Something else'),

            ('decline', 'Decline to answer'))

        gender = models.CharField(

            choices=gender_choices, max_length=10, default='male')

        maritalstatus_choices = (

            ('unmarried', 'Unmarried'),

            ('married', 'Married'))

        maritalstatus = models.CharField(

            choices=maritalstatus_choices, max_length=10, default='Unmarried')

        mobile = models.CharField(max_length=15, default='')

        alternate = models.CharField(max_length=15, default='', blank=True)

        email = models.CharField(max_length=50, default='', blank=True)

        address = models.CharField(max_length=80, default='', blank=True)

        city = models.CharField(max_length=25, default='', blank=True)

        occupation = models.CharField(max_length=25, default='', blank=True)

        bloodgroup_choices = (('apos', 'A+'),

            ('aneg', 'A-'),

            ('bpos', 'B+'),

            ('bneg', 'B-'),

            ('opos', 'O+'),

            ('oneg', 'O-'),

            ('abpos', 'AB+'),

            ('abneg', 'AB-')

            )

        bloodgroup = models.CharField(choices=bloodgroup_choices, max_length=5, default='-', blank=True)

        linkedclinic = models.ForeignKey(Clinic, on_delete=models.CASCADE)

        class Meta:

            unique_together = ["name", "mobile", "age", "linkedclinic"]

        def __str__(self):

            return self.name

        

My thoughts are on counting the number of customer objects with specific ForeignKey of a clinic, and then updating hospital id field in the same model. But probably there's a more logical less tedious way of doing this? Or should I be creating seperate tables for each clinic?

[Matthew Pava]

A number is a number, and we don’t need to attach meaning to it.

Why do you need to have continuous ids unique to each clinic?  As a developer, I would object to anyone forcing such a requirement.  If they insisted, then I would add a property that is calculated each time based on the count of patients in the clinic.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAA%3Diw_98pujPThLkq6FCqGPqJYkE1U4HF1RswQaGTv8QGc7xXw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.

[Joel Mathew]

A hospital id needs to be assigned to a patient and printed on their id card. When they present their card or this number, their records can be retrieved at any time. A checkin id is additionally generated each day based on the date and time. A patient can have only one patient id but many checkin ids.

What can be a good approach to solve this? Should I perhaps create another table just to store the last patient id issued to each clinic?

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/02ed2d48826e4249a66072c8f660e77a%40ISS1.ISS.LOCAL.

[Phako Perez]

I suggest to use another table for visit so you can add which doctor, schedule time, and comments or so as a patient may have 1 visit or n...

Sent from my iPhone

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAA%3Diw__s-3KSPizNbqNKA1dQ1tc%3DUHuQoWifvae2HTS99DRnxA%40mail.gmail.com.

[Joel Mathew]

Hi Phako,
Thank you. I'm already following this practise. I have the following
additional Model:

class Checkins(models.Model):
checkinno = models.AutoField(primary_key=True, unique=True)
hospitalid = models.ForeignKey(
customer, on_delete=models.CASCADE, null=True)
date = models.DateField(default=timezone.now)
time = models.CharField(max_length=25)
consulted = models.IntegerField(default=0)
closed = models.IntegerField(default=0)
linkedclinic = models.ForeignKey(Clinic, on_delete=models.CASCADE)

Right now, the program's policy allows any doctor of that clinic to
see a patient who checked in to that clinic, so doctor is not a
ForeignKey for this. But later I may need to add this too.

What would you suggest for adding a unique number which gets
incremented for each new patient registration, and unique to each
clinic?
Sincerely yours,

Dr Joel G Mathew

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/6C618BEA-AFD2-400F-93C0-0DC1A43EBA5E%40gmail.com.

[Matthew Pava]

Hi Dr Joel,
Each patient already has a unique number--the id or pk of the model--regardless of what clinic the patient goes to. I even recommend maintaining this structure. Any other numbers you add to the ID are just noise. Besides, a patient could go to more than one clinic. Would you want to add the ID of all the clinics that they are associated with?

If you want to see all the patients at a specific clinic, you could do something like this:
Customer.objects.filter(linkedclinic=[provided_clinic])

With your new Checkins model, I’m thinking that hospitalid and linkedclinic should be a OneToOne relation. You could also combine date and time into one field: a DateTimeField. You can always split that apart for your UI.
And each checkin also has its own ID, so you may want to use that to put on the patient's card. Do they get a new card every time they checkin?

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAA%3Diw_9in_VFvHNsDazu7WLvZP3owr%3D_rO1aSAPrvLoQx-8joQ%40mail.gmail.com.

[Joel Mathew]

Hi Matthew,
Yes, and I am using the id field to query the database for many
operations. But I do not want to share this id to the patient. The
reason is that the data of one clinic is supposed to be seperate from
another. If a patient visits another clinic using the same software,
yes, the software should generate a different id for that clinic. In
short, different ids for the same person in different clinics. Data
will not be shared between the clinics.

And no, they dont get a new card at each visit. Checkid is just used
internally to reference the data between the tables.

So how can I get a unique id for each customer per clinic? I thought
of getting the last id from the database and then incrementing one,
but that wouldnt work if the last customer was deleted. So I'm
thinking a seperate table to store the last generated hospital id, and
use this while creating a new customer record. And then incrementing
the id in the table. Is this the right approach? Is there a better
way?

To help understand what I mean, just check the GG ID printed on this
hospital card. This id can be memorized by the patient and his records
fetched and checked in.

https://imgur.com/bU4Bz8W

Sincerely yours,

Joel G Mathew

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/9c5e6dfd72734c2487d1dc63130c0fa9%40ISS1.ISS.LOCAL.

[Matthew Pava]

Hi Dr. Joel,
In that case, I would add a function on the customer model that simply concatenates the customer ID with the clinic ID. I would avoid generating a whole new table for this.

def get_card_id(self, clinic):
return f"{self.id}-{clinic.id}"

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAA%3Diw_-E%3DVQ6u7D3TwZ15H7JmZhJ1mFj1Ft9nHp1nSRjBBVOLA%40mail.gmail.com.

[Joel Mathew]

That's a great idea! Thank you.

Joel G Mathew

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/18e17766d92041c19a6c4d3b7544fe1a%40ISS1.ISS.LOCAL.

[bill.torc...@gmail.com]

Hello all,

The previous discussion shows a method that will work.  but I think it has disadvantages, and I want to suggest  another approach.

The disadvantage of using a primary key from a table, any table, is that you are committing to that table and that primary key for all eternity.   this has consequences for migration to another system , for merging in data from an external system, among others.

Joel, if I understand your situation, you have a "thing", an individual human being, and you need a permanent, unique identifier for that "thing".  The identifier should not be an implementation detail of your current code, but should be robust, sharable, and resilient in the face of evolving software.

I faced a similar situation with digital assets that are photos and videos.  We share photos and videos across several related sites, but we don't want to duplicate the asset for each site.

My solution is to generate a UUID, a Universally Unique IDentifier,  when creating the "thing" - see python 3 at https://docs.python.org/3/library/uuid.html. or python 2 at https://docs.python.org/2/library/uuid.html.

A Universally Unique IDentifier is guaranteed not to collide with any other UUID, and so independent systems can generate one at any time - no need to coordinate with a central database or across separate servers.

A UUID is robust in the face of name changes - such as a maiden name becoming a married name.

I would recommend a UUID over exposing a primary key, hands down, all the time, no exceptions.

--  Bill

[Joel Mathew]

Thank you Bill. I had a look at UUIDs. One of the important criteria I
had was that these IDs should be easily memorable. Unfortunately UUIDs
are not memorable, being too long to remember. :(

Sincerely yours,

Joel G Mathew

> --
> You received this message because you are subscribed to the Google Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at https://groups.google.com/group/django-users.

> To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/8bcbd562-5bb8-49f3-b1ad-1b73ff62d840%40googlegroups.com.

[bill.torc...@gmail.com]

Joel,

I completely agree that UUIDs are not memorable.

I still think you would be well-served to make a UUID the basis for uniquely defining a person.  If you want to further add a short name, you could make an object class that has a UUID and, say, an 8-digit number.  If you assert that the two are unique together, you will get a database-level guarantee of uniqueness at creation-time.

This is just an opinion, but I think it an anti-practice to expose a primary key for any user-visible purpose.

If you don't like UUID's for your purpose, you might consider the random number generator in PyCrypto.

[Matthew Pava]

I am curious why you think it is anti-practice to expose primary keys for user-visible purposes.

My URLs all have the PK to the object in them, and any advanced user would be able to “reverse engineer” a URL if they guess the PK of an object—and that’s okay.

Even Django tutorials suggest using the PK in the URL of an object.

I’m just curious.

 

From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of bill.torc...@gmail.com
Sent: Monday, October 22, 2018 10:20 AM
To: Django users
Subject: Re: Creating seperate unique ids within the same table

 

 

Joel,

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/eb95ee38-160e-4acb-b966-9b7f3ddd6c75%40googlegroups.com.

[Andrew Pinkham]

On Oct 22, 2018, at 12:29, Matthew Pava <Matthe...@iss.com> wrote:
> I am curious why you think it is anti-practice to expose primary keys for user-visible purposes.
> My URLs all have the PK to the object in them, and any advanced user would be able to “reverse engineer” a URL if they guess the PK of an object—and that’s okay.

https://www.owasp.org/index.php/Top_10_2013-A4-Insecure_Direct_Object_References
https://www.owasp.org/index.php/Insecure_Direct_Object_Reference_Prevention_Cheat_Sheet

> Even Django tutorials suggest using the PK in the URL of an object.

That's an interesting point, and I might argue is a shortcoming in the tutorial.

PRs welcome!

[bill.torc...@gmail.com]

Joel said this was a requirement:

One of the important criteria I had was that these IDs should be easily memorable. Unfortunately UUIDs are not memorable, being too long to remember.

 

A primary key that appears in a URL is just an implementation detail - the implementation could change, and still display the equivalent behavior to a user.

But when I person has to remember a value, that value is no longer an implementation detail.  Again, just my opinion; but that crosses the line.  

[Matthew Pava]

Very fascinating.
There is a remark from Eric Sheridan, though, that discusses this:
"I think that anytime database primary keys are exposed, an access control rule is required. There is no way to practically DOR all database primary keys in a real enterprise or post-enterprise system."
The articles very much suggest that it is the developer's responsibility to implement access control rules on objects. In Django and my projects, that's done through the authentication system, sessions, and requiring login credentials. As such, I'm not worried about exposing PKs in URLs.

Saying that, if Django really wants to discourage PKs in URLs, it needs to remove the <pk> kwarg to be used in URLs, and as you mentioned, the tutorials need to be updated.

-----Original Message-----
From: django...@googlegroups.com [mailto:django...@googlegroups.com] On Behalf Of Andrew Pinkham
Sent: Monday, October 22, 2018 11:51 AM
To: django...@googlegroups.com
Subject: Re: Creating seperate unique ids within the same table

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/79D86944-B1A3-479B-8959-9477144EBA6F%40andrewsforge.com.