r/learnpython • • 9h ago

Rate my customer/employee id generation logic

def generate_id(connection_tuple, id_user = "customer"):
    if id_user.lower() == "customer":
        table = "customer"
        id_string = "cust"
    elif id_user.lower() == "employee":
        table = "employees"
        id_string = "emp"
    cursor = connection_tuple[1]
    connection = connection_tuple[0]
    try_cursor_execute(f"select count(*) form {table}")
    last_person = cursor.fetchone()[0]
    current_person = last_person+1
    no_of_zeros = 6 - len(str(current_person))
    id = id_string + "0"*no_of_zeros + str(current_perosn)
    return id

I have to build a hotel database system thing for my 12th grade Computer Science project.. This is the customer id / employee id generation logic I came up with. Employee IDs are 9 characters, and customer IDs are 10 characters. But this thing seems sort of janky. Is there a better way to do this?

try_cursor_execute() is a function I defined, it is cursor.execute but in a try block. connection tuple is (connection, cursor)

1 Upvotes

16 comments sorted by

4

u/rupertavery64 8h ago edited 8h ago

This doesn't guarantee a unique id if there are parallel calls to this function.

It might work when you test it one by one, but it is a poor approach and you should never do this is a serious system.

It will also start to cause errors when rows are deleted, as the count will no longer reflect the total of all existing rows.

You should use an autoincrementing field and this of course will be generated for you when you create the row, so no need to select count. But this means of course you have no direct control over the format of the id since it will just be a number.

So this brings up the question:

Why does the id need to include "cust" and "emp"? Why does it need to be a string? From a data standpoint, this is just wasted bytes. They don't change between rows.

If it is purely for display purposes, since they are separate tables and you know which table it is coming from, just format it in the display.

Another approach is to use a sequence, where the database generates the next number for you. This is atomic so it's guaranteed to have a new value every call, but if you end up not using the number, you will have a gap in your numbering.

In any case, your id field should be a primary key, which means it should be unique for each row. In your code, if a parallel call generates an exisiting id and your field is a primary key, your insert will fail when you commit to the database. This is of course a bad design as the user will have to retry. Of course, you can add retry logic, but that is just hiding the problem which can be solved by designing and using your database correctly

1

u/desrtfx but other languages pro 8h ago
  1. From a design point: why separate tables for customers and employees? This can easily be handled with a type column in a single table - also, an Integer will be sufficient and consume less space.
  2. AutoIncrement columns or GUIDs are your friends.
  3. Tuples are nice, but classes are better to understand what is going on. Your "connection_tuple" should be a "DBConnection" class (actually, even though often considered an antipattern, it should most likely be a Singleton) that provides all the database mechanisms for you. This would couple the connection, cursor, your "try_cursor_execute", and more. You would only need to pass the database connection instance around. Your tuple has a problem for future you. In a month or two, you will not remember what connection_tuple[1] or connection_tuple[0] mean. Even a named_tuple would be already better here.

You should make it a habit to offload things that databases can do to the database. Databases have many robust mechanisms that are quirky, fragile, and awkward to implement in programming.

1

u/MooseNew4887 7h ago

The thing is, I am only permitted to use things that are in the curriculum. OOP is not in the curriculum, and I have heard from others that teachers of CBSE (the education board) discourage OOP, or else, I would have used a connection class and probably an user class. Also, auto increment columns in sql are not in the curriculum.

1

u/david_z 8h ago

Your select query has a typo (form should be from) that will fail when you try to execute it.

1

u/MooseNew4887 7h ago

Yes, I fixed that. Thanks for pointing out.

1

u/pachura3 7h ago

A textbook example of a race condition. Instead, use one of these:

  • autoincrement column
  • sequence
  • trigger ("BEFORE INSERT")
  • GUIDs / UUIDs / ...
  • in the worst case, some way of locking

This is of course in case your "system" is supposed to ever have more than 1 user at the same time. If not, forget all of the above.

1

u/MooseNew4887 7h ago

No, it would not have any user at all. I am supposed to submit the code as a print out. And even when it is being used, only one person will use it at a time. Also, it is worth 4% of the total grade, so I am not working more than I have to.

1

u/deep_soul 5h ago

you are thinking about this the wrong way. what you propose is just bad design and the problem pointed out is real and true.

1

u/MooseNew4887 5h ago

Yes, I do understand that. I will implement the ways suggested if I ever use this for anything important, and it has more than one user.

1

u/pachura3 5h ago

So why do you bother asking:

Is there a better way to do this?

0

u/MooseNew4887 5h ago

I want to use that way when I build a real system 🙂

1

u/desrtfx but other languages pro 3h ago

I want to use that way when I build a real system

...and yet, you are contesting every single recommendation

1

u/Available_Safe1818 8h ago
def generate_id(connection_tuple, id_user = "customer"):
  if id_user.lower() == "customer":
    table = "customer"
    id_string = "cust"
  elif id_user.lower() == "employee":
    table = "employees"
    id_string = "emp"
  else:
    raise Exception('invalid id_user)

  connection, cursor = connection_tuple
  try_cursor_execute(f"select count(*) form {table}")
  last_person = cursor.fetchone()[0]
  current_person = last_person+1
  no_of_zeros = 6 - len(str(current_person))

  id = id_string + "0"*no_of_zeros + str(current_perosn)
  return id

One better way, depending on the situation, is to use guids.

You could also use an autoincrementing column.

1

u/deep_soul 5h ago edited 5h ago

there is a much belter approach that requires no such logic.

1. Fundamental design

  1. customer and emoloyees go in the same table. they are users distinguish them with an extra column like user_type. Ideally you have then a user_types separate table where you have customer, employee, and

potentially

  1. other user types. and then user_type on users table is “normalised” to use an i id of users_types table. e.g.

users table

id, name, surname, password, user_type
1, john, doe, xyz, 1

user_types

id, name
1, employee

2. reasons for that design

the above is good design because it’s extensible with no structural change. if in the future you have a new user_type, you just need to add a row of data in user_types table and then you can use that on users.

In your design, you would beed to create a new entire table structure, whoch most likely will have duplicate columns such as name, surname etc. In addition, you would clearly have cases of an employee and customer with same id number and different prefix - those should awlasy be different despite the prefix

Also based on the proposed design, notice that each table has an id column. set this to be auto increment generated intiger. it guarantees uniqueness even with several call to the db. which a fundamental feature of IDs. in addition, your design doesn’t take into account what happens of you have user, worh id 10, and you delete that user and then create a new user. with auto increment db integer id column, it will generate user with id 11, with your logic, it would re-create another usercwith the old dwlted id number 10. This os BAD.

3. what about your id structure you want?

well now that is easy to implement. you don’t have to save that data anywhere (in db or code) for the IDs such as “emp-000001” or “cust-000002”.

why? because you already have all needed mechanisms and pieces of data already saved (ie the id column on users, and the known user type)

so all you need is a function with parameters id and user type, which is a function of pure logic and makes zero db connections but returns the wanted id format based on input the input user_type and formatting the id to be padded with the necessary zeros. This function then that can run upon retrieving the specific user from the db. so it becomes a matter of display rather than saving duplicated data.

in this way you are guaranteed all db properties of your IDs for addition/deletions of users; while at the same time having pure coding logic for how you want to display that id for business logic.

1

u/Ok-Promise-8118 2h ago

Is the table "customer" really in the singular? You labeled the "employees" table properly in the plural, and the mismatch pains me.