Django generate unique sequence for each organisation in a SaaS app

[Sid]

I'm working on a django SaaS app, which hosts data for multiple
organisations. Each record in a table has a unique RecordNumber.

I could use autofield to give each record a unique incrementing Id
automatically, but that is globally unique. As in org1 might have say,
RecordNum=1 or 2 or 7 etc and org2 might have RecordNum=3,4,5,6

I want each organisation to have its own sequence, i.e. all
organisations might have RecordNum=1 or 2...etc. I can still use
autofield as primary key to identify the row. But i need the RecordNum
field as it will be visible to the end users as sequence numbers
within their org.

UUID, are too cumbersome to type in while searching for a record.

I found this - http://stackoverflow.com/questions/1104741/generating-sequential-numbers-in-multi-user-saas-application
But i before resorting to stored procedures, i want to make sure there
isn't any other way.

Ohh and btw i'm on MySQL.

[Michael Manfre]

You can do this without a stored procedure, but it will require two db
queries.

Create a custom field with an overridden pre_save that fetches the
last RecordNum for the org and returns the next in the sequence.
You'll want an index on org and recordnum.

class AutoRecordNumberField(models.IntegerField):
def pre_save(self, instance, add):
if not add:
return getattr(instance, self.attname)
return getattr(instance, 'next_available_recordnum')

class TheModel(models.Model):
org = ...
recordnum = AutoRecordNumberField()

@property
def next_available_recordnum(self):
try:
num = TheModel.objects.filter(org=self.org).values_list
('recordnum').order_by('-recordnum')[0]
except:
return 1

return num[0] + 1

On Sep 15, 4:05 am, Sid <sidmitra....@gmail.com> wrote:
> I'm working on a django SaaS app, which hosts data for multiple
> organisations. Each record in a table has a unique RecordNumber.
>
> I could use autofield to give each record a unique incrementing Id
> automatically, but that is globally unique. As in org1 might have say,
> RecordNum=1 or 2 or 7 etc and org2 might have RecordNum=3,4,5,6
>
> I want each organisation to have its own sequence, i.e. all
> organisations might have RecordNum=1 or 2...etc. I can still use
> autofield as primary key to identify the row. But i need the RecordNum
> field as it will be visible to the end users as sequence numbers
> within their org.
>
> UUID, are too cumbersome to type in while searching for a record.
>

> I found this -http://stackoverflow.com/questions/1104741/generating-sequential-numb...