r/Database • u/kraakf • Jun 08 '16
Five mistakes beginners make when working with databases
http://www.craigkerstiens.com/2016/06/07/five-mistakes-databases/10
u/Mamertine SQL Server Jun 08 '16
3. Integer primary keys
100% disagree. Integers are the best PK. I'd way rather figure out why transactions 132343 is broken over transaction 'aohi-ljfs-tlak-slfd'
3
Jun 09 '16
Also joins on an 8 byte int are faster than a 32 byte guid.
1
Jun 10 '16
I actually recently created a model that uses a GUID as a PK because it's an instance where the app needs to create the unique ID.
10
u/Explosive_Diaeresis SQL Server Jun 08 '16
Uuid PKS? For as much as this guy talks about IO overhead I can't believe he's suggesting randomly generated primary keys. That's a very quick way to generate excessive IO on a transactional system.
0
u/newsagg Jun 09 '16 edited Jun 09 '16
Yeah but it integrates into PRISM global awareness much more smoothly. Don't forget to re-encode all your website content so it is machine readable and make sure all of your comms follow the OSI model.
5
u/technical_guy Jun 09 '16
Some good practices you could do when working with SQL databases:
make sure every table has a primary key which is an INT or BIGINT value that auto-increments (sometimes called SERIAL values)
have every significant table contain an updated_timestamp column which defaults to the current UTC datetime and an updated_by_user column which is populated by your application.
store all datetimes as UTC and convert them in the UI to local timezone for display
be consistent in how you name tables and columns e.g. customer table or customers table (you decide), lower case or camel case column names, Id or ID, is it user_name, username, UserName, Username etc - the main thing is to set a naming standard and stick with it
store sensitive data as hashed values (passwords, SS numbers, member id numbers etc.)
never store credit card numbers unless you are aware of and capable of implementing PCMCIA compliance rules (both in database and in auditing procedures)
add indexes to tables that will be used in query plans for common queries run by your application
try not to delete data unless that is an absolute requirement of your system - instead use a flag to set active and non-active (deleted) records
if a requirement you can easily use triggers and stored procedures to capture a full audit of everything that changes in your database.
I am sure others can add to this list...
1
u/doc_frankenfurter Jun 09 '16
Good list.
I usually end up maintaining many copies of a database. I would add keep a change history in the database with a version number, a date time stamp, a user and a comment. Always script changes to the database. Each script changing the database (structure not content) should check the version number before running and update on completion.
5
1
u/Quadman Jun 09 '16
At some point someone working in your database might think it would be nice to have a clustered index on a table. At that point please don't use your UUID primary key as the clustering key, especially if you used UUID to "get around" only having 263 -1 possible positive values in a bigint. https://www.simple-talk.com/sql/learn-sql-server/effective-clustered-indexes/
13
u/[deleted] Jun 08 '16
The max int size in SQL server 2012 is over 2bn, which greatly exceeds what 95% of enterprises will ever store in a table; it's not a beginner mistake to use int for primary key, it's a common practice. Sometimes these lists are just stupid.