r/Database • • Feb 14 '12

20 Database Design Best Practices

http://www.javacodegeeks.com/2012/02/20-database-design-best-practices.html
0 Upvotes

18 comments sorted by

4

u/Tordek Feb 14 '12

Keep passwords as encrypted for security. Decrypt them in application when required.

No.

3

u/[deleted] Feb 14 '12

My comments:

1.) this is good advice

2.) This is 100% personal preference and makes no difference whatsoever

3.) Agreed that you shouldn't use spaces, but sometimes there are good reasons to if you're exposing table names in another layer to non-programmers/dbas

4.) Agreed to some extent - it's common practice to use a prefix to denote a view or stored procedure, but like 2.), this is more a personal preference thing

5.) Oh lord no. The word decrypt should never be in the same sentence as the word password.

6.) Agreed, but keep in mind when you have a lot of data integer isn't going to work - you'll need bigint at that point (and in some very rare cases where you need to be assured that your IDs are completely unique you should use GUIDs)

7.) This is blatantly wrong advice. Yes, an index on an integer column will perform better than one on a varchar column, but doing pretty much anything with an integer column will perform better than a varchar column as it's (usually) less data to read. If you have a query that uses a varchar comparison in your WHERE clause (or another action similar to WHERE such as CASE), you should index the varchar since WHERE col1 = 'exampleword' won't be improved by indexing an integer. Maybe he meant to say make your primary key a numeric column?

8.) Agreed, but every once in awhile I've seen a 3rd party program freak out from this

9.) No shit

10.) You should almost never need SELECT * in an application query

11.) no comment

12.) What? If they're unused/rarely used it doesn't matter where they're stored. Partition big and frequently used tables across different storage arrays for better performance.

13.) Well, yes, but this is kind of a well duh practice, and it's not specific to databases - anything that's mission critical should be redundant if possible.

14.) This is a good idea, but it can wreck performance if you're doing large inserts or need the fastest possible db speed.

15.) Yes - just like all other code. Not really db specific.

16.) Nonclustered indexes refer back to clustered index keys if the clustered index is in place first, so this isn't entirely true...

17.) Absolutely correct

18.) As others have pointed out, this is incredibly obsolete advice. I think he's trying to get at normalization here, but it's not obvious...

19.) Ah here's the normalization item. Agreed, but he forgot to mention that the level of normalization you use is dependent on how much replicated data you're storage array will allow you to store balanced with how fast you want to get the data out. Dimensional star schemas seem to work best for data marts, but your warehouse will likely be a bit more normalized than that.

20.) Meh, yes and no. Don't rush in without thinking about design at all, but you can waste a ton of time trying to design every possible facet of the database rather than letting some design elements arise from discovery of need

1

u/[deleted] Feb 14 '12 edited Jul 28 '12
  1. some_entity is a better naming convention in my mind: some databases (postgresql afaik, or at least the php driver) doesn't do camelcase, so if you use activerecord, it won't work

  2. app login passwords are hashed, not encrypted, unless you are storing them for another application (like logging into an external web service)

  3. 7 is totally wrong. any field you use in a where clause should be indexed, except boolean fields.

pretty sure 18 is wrong unless you are select * ing the table and getting the blob on every request, which is dumb

"over" normalization results in slower selects sure, but you aren't building a f-in data warehouse (where you purposefully denormalize). inserts and updates and deletes should be faster.

1

u/Rolegros Feb 14 '12

FYI, postgresql does support case sensitive names, it's just that you have to quote them appropriately.

If you create a table as create table camelCasedTable, your table will in fact be named camelcasedtable. On the other hand, if you create it as create table "camelCasedTable", it will carry the proper capitalization.

If your tool doesn't quote identifiers correctly, you should avoid any special names.

1

u/taejim SQL Server Feb 14 '12

With 18, SQL Server 2005+ will store the large object data "off-row" in a separate structure, essentially to avoid the problem 18 is trying to solve. Not sure about other database engines, but I'm sure they'll do similar things.

1

u/[deleted] Feb 14 '12

Just realize that for touch-typers, underscores are hell.

0

u/[deleted] Feb 14 '12

[deleted]

1

u/[deleted] Feb 14 '12

What keyboard doesn't have the underscore under the hyphen in the top right corner?

1

u/mikaelhg Feb 14 '12

1

u/[deleted] Feb 14 '12

Oh that's right.

That keyboard makes underscores worse. (Left weakest finger and right weakest finger have to operate in concert)

1

u/artsrc Feb 15 '12

Best practice are premature over engineering. Best practice is to avoid best practices.

Best practices about performance are premature optimization, and are thus the root of all evil.

For example:

Database server and the web server must be placed in different machines. This will provide more security (attackers can’t access data directly) and server CPU and memory performance will be better because of reduced request number and process usage.

Most systems are low volume, internal only. So in general this will reduce performance by adding latency.

1

u/[deleted] Feb 14 '12

This is a really poor list -- most entries are 'duh', others are highly questionable:

Use bit fields for boolean values. Using integer or varchar is unnecessarily storage consuming.

Bitfields -- really? Individual Boolean columns I can appreciate, but bitfields [ coming from a postgres background here ]

3

u/taejim SQL Server Feb 14 '12

I read this as "Columns of type 'bit'", which makes sense in the SQL Server world - 1-8 bit columns takes up a single byte, whereas 8 integers storing a 1 or a 0 would consume 32 bytes. Really, this comes down to "use the smallest data type you can get away with".

Looking at the Postgres Boolean documentation, your Boolean looks functionally equivalent to SQL Server's bit, except Postgres takes up one byte per column. While SQL Server does understand a Boolean data type, it doesn't support them inside tables or result sets.

1

u/[deleted] Feb 14 '12

OK, that makes more sense. Thanks for the clarification.

1

u/[deleted] Feb 14 '12

Except for "don't use booleans" ...

1

u/[deleted] Feb 14 '12

Are you saying that "Don't use booleans" is good advice? If so, what's your reasoning there?

0

u/[deleted] Feb 14 '12

[deleted]

5

u/AQuietMan PostgreSQL Feb 14 '12

Consider the source: Java Code Geeks.

I once had a Java programmer tell me, "Just give me five nullable integer columns, and I'll decide what to do with them."

This is not a list of best practices.

Probably better called "The only 20 things I've ever heard about databases".

0

u/[deleted] Feb 14 '12

[deleted]

2

u/spuur Feb 14 '12

Several of the recommendations are the 180º degree direct opposite of what experienced black belts like Joe Celko recommend -so yeah...