5 ms·

Do you make these 5 database design mistakes?

54 points by SQLRockstar 15y ago

ams6110 15y ago
I think more important than obsession over column sizes (in most databases, varchar columns only use the space necessary, not the maximum, more or less) is using correct datatypes. E.g. using varchar for a column that you know will only have numbers. Or using int for timestamps (e.g. using the unix epoch offset) and then having to do a lot of converting. Or sticking serialized objects from your application domain into blob columns. Most databases have a lot of built in safety and functionality if you are using the correct datatypes, but that is something you can't benefit from if you are treating everything like a blob or a string.
- deleted 15y ago
  [deleted]
- InclinedPlane 15y ago
  A rule of thumb: if you ever end up with a situation where you want to display data that is essentially just a table and you can't drive that via a fairly simple sql query then that probably means you need to revisit your design.
- noblethrasher 15y ago
  "using varchar for a column that you know will only have numbers" I've been bitten too many times by the recommendation of using numeric types for [0-9]+ data. Unless it's fundamentally going to involve arithmetic I say leave it as some kind of string type.
- jbigelow76 15y ago
  I've got to disagree about not using varchar for a column you you know will only contain numbers. I'm assuming you mean something like the numbers of a phone number (dashes and parens have been scrubbed out). Even if a field will only contain numbers I won't use a numeric based field type unless there is the possibility that I would be using the value in some form of mathematical function. I might add and subtract from an account balance but I would never add or subtract from a Social Security Number or zip code. (I don't keep to this rule for surrogate keys which will be int/bigint auto incrementing, even though I won't be doing any math on the values themselves)
  - onemoreact 15y ago
    There is value in what your saying. But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Use it as an index and you waste that space again, store it in a cache and you have less space for useful information, compare it with another SSN and it's slower etc. Now individually it's probably irrelevant but start doing this with several fields on a large application and you can noticeably slow things down even if the extra disk space seems cheap. PS: 95% of the time it's probably the safest option, but it's still something you should consider based on how that data fit's in with the rest of your application.
    - kls 15y ago
      Some numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.
      - Tsagadai 15y ago
        Phone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.
        ListMistress 15y ago
        I agree, neither phone numbers nor social insurance numbers are unique. But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes. This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.
jtchang 15y ago
I tend to view designing database schemas as an evolving process. Picking larger datatypes to me is sometimes easier than having to change it at a later date. If space really does become an issue I can deal with it later. You need to really understand the data you plan to put in.
swah 15y ago
I never took databases on college, don't have energy to learn about normal forms, but find "designing" the schemas for my simple services a joy.
- GlennS 15y ago
  I do see that you can go a long way with relational databases just using experience. However, they do have a formalised mathematical basis and I think you dismiss it too quickly. If you do spend the time to learn the maths then it will push your abilities that little bit further. You'll also likely find it quite easy to pick up given that you already understand the field well.
cleaver 15y ago
One mistake I used to see a lot is not paying attention to indices. It's sort of like the "go big" error in that you just create indices all over the place even if they could never be used. Also, it could be adding columns to the index that won't improve access time, or not paying attention to the order of columns. In reality, you need to analyse your code and profile your application to see what actually is needed for your indices. Also essential is understanding the overhead that an index creates. I don't see this as often today, however. I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes.
- rhizome 15y ago
  I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes. Which is perfectly fine.
  - cleaver 15y ago
    Agreed. And certainly better than creating bad indices.
    - glhaynes 15y ago
      I wonder: are there any good rules-of-thumb for when to go ahead and add indices at table-creation time?
      - emmett 15y ago
        Projected usage. It can be done, but you have to be an expert basically.
      - BrentOzar 15y ago
        Add an index to support foreign key relationships. If you've got a SalesHeader table and a SalesDetail table, you want an index on SalesDetail.SalesHeaderID, especially if you allow cascading deletes from the SalesHeader table.
dnewcome 15y ago
I've got to push back on the anti-guid stance for surrogate keys. They may be big but it is nice to have opaque IDs so that no one makes flawed assumptions about ordering or relative insertion time/trying to guess valid IDs, etc. Opaque is good...
- mafro 15y ago
  This was my sentiment exactly. Any idea why he's so keen on sequential keys? They're mentioned as desirable several times in the article.
  - matclayton 15y ago
    The issue, particularly with innodb on MySQL is that the row data is stored with the primary key. By using GUID's and not sequential ids, you end up having to rearrange the entire dataset to insert the row data at the appropiate place, to keep the index in order, instead of appending it, killing your write performance.
- ListMistress 15y ago
  GUIDs are HUGE and have a significant impact on performance. Go read Kimberly Tripp's blog post referenced in the article. She does the math for you. In almost all the cases that I see GUIDs, they were totally unnecessary for the design. Even the developer who designed them could not give a reason why they needed to be GUIDs. A row unique across the entire universe? Really? I'm not saying there are no cases...just that in most cases they negatively impact performance with little business or logic gain for that price. All design decisions come down to cost, benefit and risk.
  - jaylevitt 15y ago
    How do modern distributed OLTP systems deal with generating unique sequence numbers? Back in the day, this was a big problem, and I always thought that GUIDs would someday be a solution (though at the time, any string was too big/slow to be a primary key). Having one key-issuing server was a SPF; sharding the key ranges by server made it difficult to add new servers.
zbowling 15y ago
This webpage causes my chrome to lock up and my laptop fan to go on full strength. Heavy HEAVY social media and user tracking javascript.
dspillett 15y ago
On "not going big, just in case" I don't disagree, but the body text implies that excessive use of storage space is the problem you are creating for yourself, which isn't the most impoartant factor here by quite a margin. Space is cheap. What aren't as cheap are memory and I/O bandwidth: using large datatypes limits the size of working-set you can fit into a given amount of memory, and slows down the process of reading data from permenant storage into memory when needed and not already present. Increasing the load on your I/O capability in this way is far more of a problem than the extra storage space consumed.

54 points by SQLRockstar 15y ago

I think more important than obsession over column sizes (in most databases, varchar columns only use the space necessary, not the maximum, more or less) is using correct datatypes. E.g. using varchar for a column that you know will only have numbers. Or using int for timestamps (e.g. using the unix epoch offset) and then having to do a lot of converting. Or sticking serialized objects from your application domain into blob columns. Most databases have a lot of built in safety and functionality if you are using the correct datatypes, but that is something you can't benefit from if you are treating everything like a blob or a string.

deleted 15y ago
[deleted]
InclinedPlane 15y ago
A rule of thumb: if you ever end up with a situation where you want to display data that is essentially just a table and you can't drive that via a fairly simple sql query then that probably means you need to revisit your design.
noblethrasher 15y ago
"using varchar for a column that you know will only have numbers" I've been bitten too many times by the recommendation of using numeric types for [0-9]+ data. Unless it's fundamentally going to involve arithmetic I say leave it as some kind of string type.
jbigelow76 15y ago
I've got to disagree about not using varchar for a column you you know will only contain numbers. I'm assuming you mean something like the numbers of a phone number (dashes and parens have been scrubbed out). Even if a field will only contain numbers I won't use a numeric based field type unless there is the possibility that I would be using the value in some form of mathematical function. I might add and subtract from an account balance but I would never add or subtract from a Social Security Number or zip code. (I don't keep to this rule for surrogate keys which will be int/bigint auto incrementing, even though I won't be doing any math on the values themselves)
- onemoreact 15y ago
  There is value in what your saying. But, if you store a SSN as say 123-45-6789 your using at least 11 bytes to store a 4 byte number. Use it as an index and you waste that space again, store it in a cache and you have less space for useful information, compare it with another SSN and it's slower etc. Now individually it's probably irrelevant but start doing this with several fields on a large application and you can noticeably slow things down even if the extra disk space seems cheap. PS: 95% of the time it's probably the safest option, but it's still something you should consider based on how that data fit's in with the rest of your application.
  - kls 15y ago
    Some numeric data types will trim off leading 0's so if you have a number like 00543 when stored in the DB it will be retrieved as 543. Things like social security numbers and phones numbers are unique identifiers that just so happen to be numeric they should be treated as such with regards to data considerations.
    - Tsagadai 15y ago
      Phone numbers are not unique identifiers. In several regions around the world, phone numbers are reused. Please keep this in mind because it is very short sighted to say you will never expand globally.
      - ListMistress 15y ago
        I agree, neither phone numbers nor social insurance numbers are unique. But the issue is with storing them in numeric datatypes. They aren't guaranteed to be unique forever (and indeed, are alphanumeric in many jurisdictions) and the leading zero problem is a killer when you go to reconstitute them, both for performance and for logic reasons. Same goes for US ZipCodes. This is why I have to have a very strong technical reason to make a non-math column numeric, especially with externally set data (like SSNs, SINs, Account codes, etc.) The people who set them could just start adding letters or symbols...and this isn't rare.

jtchang 15y ago

I tend to view designing database schemas as an evolving process. Picking larger datatypes to me is sometimes easier than having to change it at a later date. If space really does become an issue I can deal with it later. You need to really understand the data you plan to put in.

swah 15y ago

I never took databases on college, don't have energy to learn about normal forms, but find "designing" the schemas for my simple services a joy.

GlennS 15y ago
I do see that you can go a long way with relational databases just using experience. However, they do have a formalised mathematical basis and I think you dismiss it too quickly. If you do spend the time to learn the maths then it will push your abilities that little bit further. You'll also likely find it quite easy to pick up given that you already understand the field well.

cleaver 15y ago

One mistake I used to see a lot is not paying attention to indices. It's sort of like the "go big" error in that you just create indices all over the place even if they could never be used. Also, it could be adding columns to the index that won't improve access time, or not paying attention to the order of columns. In reality, you need to analyse your code and profile your application to see what actually is needed for your indices. Also essential is understanding the overhead that an index creates. I don't see this as often today, however. I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes.

rhizome 15y ago
I think that in a lot of cases developers put off creating indices of any sort until a performance problem materializes. Which is perfectly fine.
- cleaver 15y ago
  Agreed. And certainly better than creating bad indices.
  - glhaynes 15y ago
    I wonder: are there any good rules-of-thumb for when to go ahead and add indices at table-creation time?
    - emmett 15y ago
      Projected usage. It can be done, but you have to be an expert basically.
    - BrentOzar 15y ago
      Add an index to support foreign key relationships. If you've got a SalesHeader table and a SalesDetail table, you want an index on SalesDetail.SalesHeaderID, especially if you allow cascading deletes from the SalesHeader table.

dnewcome 15y ago

I've got to push back on the anti-guid stance for surrogate keys. They may be big but it is nice to have opaque IDs so that no one makes flawed assumptions about ordering or relative insertion time/trying to guess valid IDs, etc. Opaque is good...

mafro 15y ago
This was my sentiment exactly. Any idea why he's so keen on sequential keys? They're mentioned as desirable several times in the article.
- matclayton 15y ago
  The issue, particularly with innodb on MySQL is that the row data is stored with the primary key. By using GUID's and not sequential ids, you end up having to rearrange the entire dataset to insert the row data at the appropiate place, to keep the index in order, instead of appending it, killing your write performance.
ListMistress 15y ago
GUIDs are HUGE and have a significant impact on performance. Go read Kimberly Tripp's blog post referenced in the article. She does the math for you. In almost all the cases that I see GUIDs, they were totally unnecessary for the design. Even the developer who designed them could not give a reason why they needed to be GUIDs. A row unique across the entire universe? Really? I'm not saying there are no cases...just that in most cases they negatively impact performance with little business or logic gain for that price. All design decisions come down to cost, benefit and risk.
- jaylevitt 15y ago
  How do modern distributed OLTP systems deal with generating unique sequence numbers? Back in the day, this was a big problem, and I always thought that GUIDs would someday be a solution (though at the time, any string was too big/slow to be a primary key). Having one key-issuing server was a SPF; sharding the key ranges by server made it difficult to add new servers.

zbowling 15y ago

This webpage causes my chrome to lock up and my laptop fan to go on full strength. Heavy HEAVY social media and user tracking javascript.

dspillett 15y ago

On "not going big, just in case" I don't disagree, but the body text implies that excessive use of storage space is the problem you are creating for yourself, which isn't the most impoartant factor here by quite a margin. Space is cheap. What aren't as cheap are memory and I/O bandwidth: using large datatypes limits the size of working-set you can fit into a given amount of memory, and slows down the process of reading data from permenant storage into memory when needed and not already present. Increasing the load on your I/O capability in this way is far more of a problem than the extra storage space consumed.