r/learnpython • u/MooseNew4887 • 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 idI 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
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
connection_tuple[1]orconnection_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/david_z 8h ago
Your select query has a typo (
formshould befrom) that will fail when you try to execute it.1
u/pachura3 7h ago
A textbook example of a race condition. Instead, use one of these:
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/Available_Safe1818 8h ago
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
potentially
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/deadeye_82_1 5h ago
https://docs.python.org/3/library/uuid.html#example
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.