sql - Table design with "Person Number" -


we have need store people, 'person number' in format business uses them. @ moment, use manual process numbers person in format of, example fp123456. next person added gets next number, fp123457.

there search in system based on number.

my idea use primary key numeric part, identity(123456,1), , have calculated column (somehow), appends current prefix (fp) primary key, giving fpxxxxxx

i thinking of maybe using default (if that's possible) on personnumber column, which, on inserts, 'fp' + id.

is idea, or there possible issues? also, how default column, on insert. maybe trigger better?

i keep personnumber separate primary key.

you find folks disagree, in experience using natural key primary key always turns out bad choice. things change*.

  • today format personnumber 1 way. tomorrow new boss new ideas, , format changes. have problem of updating not 1 original column "personnumber", have update foreign keys well.
  • tomorrow may need federate, meaning share records across multiple databases or other systems. have change way primary key, such using uuid values rather integer values.
  • at point may need rebuild tables, involving exporting , importing data. @ point want keep personnumber values generate new primary key values.

bottom line job of generating primary key may seem same kind of job generating personnumber, 2 different jobs. separation of concerns guiding principal avoid brittle (easily broken) software. example, if personnumber generator gets messed somehow, @ least rest of database (primary keys in table , foreign keys in related tables) still continue function.

so use surrogate key primary key. separately work out strategy managing personnumber.

that strategy may involve user interface or server-side app incrementing number generate personnumber, suggested sdreyes on page.

another strategy leveraging database server generate sequence/serial personnumber. if have serious database server such ms sql server or postgres, should able create sequence/serial generator without being tied primary key. can create trigger calls on sequence generator assign default value new records. serious database server built handle concurrency problem pointed out sdreyes numbers generated without duplicates. read doc, serial number generators may have gaps in sequence, if transaction rollback happens. if unacceptable, may need find route.

*if think unlikely, ask programmer or sysadmin grey hair.


Comments

Post a Comment

Popular posts from this blog

basic authentication with http post params android -

i doing basic authentication passing username , password , using basicnamevaluepair sending post params response service. my method: public stringbuilder callservicehttppost(string username, string password, string type) { // create new httpclient , post header httpclient httpclient = new defaulthttpclient(); httppost httppost = new httppost(webservice + type); httpresponse response = null; stringbuilder total = new stringbuilder(); try { url url = new url(webservice + type); /*string base64encodedcredentials = base64.encodetostring((username + ":" + password).getbytes(), base64.url_safe | base64.no_wrap);*/ string base64encodedcredentials = "basic " + base64.encodetostring( (username + ":" + password).getbytes(), base64.no_wrap); httppost.setheader("auth...
Read more

vb.net - Virtual Keyboard commands -

i wondering difference between virtual these keyboard commands is: keyeventf_extendedkey , keyeventf_keyup is. everywhere have looked gives me description based off integers , not want know each of them does. you've tagged question vb.net, these have nothing @ vb.net. they're constants defined in windows header files, use win32 api functions. as far difference, can't tell looking @ values. individual values not particularly important, that's why named identifiers used. what's important used , documentation functions tells mean. the first one, keyeventf_extendedkey , used keybdinput structure (which used along e.g. sendinput function) pass information synthesized keyboard input. if flag used, means scan code should interpreted extended key. technically, means scan code preceded prefix byte value 224 (&he0 in hexadecimal notation). the second one, keyeventf_keyup , 1 of flags available use structure. means key being released (going up)...
Read more

css - Firefox for ubuntu renders wrong colors -

Image
i've got following problem: i'm developing website, while testing around i've noticed firefox , chromium display css bar in different colors. when switched between several machine come conclusion firefox ubuntu problem. on other devices i've tested website. it's fine. the differences between chrome , firefox rendering: firefox: chromium: note: firefox ubuntu problem. does knows why , came solution? https://bugzilla.mozilla.org/show_bug.cgi?id=655637 bug 629312 - firefox displays wrong colors on image (only linux) open about:config , rechange gfx.color_management.mode 2 0.
Read more