Best Praxis for “user-id/primary”
-
Hello experts I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain). Why? The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type…. Ok, long speech. What I’m really like asking for Best Praxis is: I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default. In case this “user-id” allows alphanumeric, what is better/best Praxis/usual: a.) Make Ids case sensitive ? b.) Make Ids case insensitive? Thank you in advance Bruno
-
Hello experts I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain). Why? The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type…. Ok, long speech. What I’m really like asking for Best Praxis is: I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default. In case this “user-id” allows alphanumeric, what is better/best Praxis/usual: a.) Make Ids case sensitive ? b.) Make Ids case insensitive? Thank you in advance Bruno
Since you can do "like" and "sounds like" searches in SQL Server, you need to review your "requirements" more. For the record, the recommended approach for "entity keys" are "nonsense numbers"; i.e. identities / generated id's. And they're central to Entity Framework's "tracking" ability. It's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email; in which "case", you can case it the way you want when comparing.
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
Since you can do "like" and "sounds like" searches in SQL Server, you need to review your "requirements" more. For the record, the recommended approach for "entity keys" are "nonsense numbers"; i.e. identities / generated id's. And they're central to Entity Framework's "tracking" ability. It's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email; in which "case", you can case it the way you want when comparing.
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
Thank you, I need to think more about
Quote:
's the "user" ids / keys that get transported between systems; so they better match (case-wise); unless it's "universal", like an email.
Thanks again Bruno
I find that the more systems I'm integrating with, the more I rely on creating "API's" where the object "getters" do some of the translating on the fly for a better or cleaner "view" within the context of the application. Molding reality.
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
Hello experts I’m usually organizing the data internally using a “system” generated id (at the moment int generated by mssql sequences) to define primary keys and foreign key relations. That has sometimes advantages but on the other hand it is not really the clean way from the point of theory (and for data exchange it can be the real pain). Why? The user usually needs also to have his “user-id” which is the user’s primary… so with the way above I introduce an extra index. And basically the user primary should enough and should be used to define the db constraints. But programmers are lazy and like keys all of the same type…. Ok, long speech. What I’m really like asking for Best Praxis is: I’m at a point to migrate Interbase databases to MSSQL. Interbase has as default case sensitive indices while MSSQL has case insensitive as default. In case this “user-id” allows alphanumeric, what is better/best Praxis/usual: a.) Make Ids case sensitive ? b.) Make Ids case insensitive? Thank you in advance Bruno
Store all in upper-case and go for insensitive. Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it? For primary keys, I still recommend normalization. Your auto-generated identity is of little use to the user (and should not be visible).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Store all in upper-case and go for insensitive. Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it? For primary keys, I still recommend normalization. Your auto-generated identity is of little use to the user (and should not be visible).
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^][](X-Clacks-Overhead: GNU Terry Pratchett)
-
Quote:
Wouldn't make much sense most of the time to have a record with and one without capitalization in the database, would it?
That is exactly the Point for which I'm looking for. Completely agree with you. Thank you for confirming this.