5 ms·

Seriously. Data types -- surely anyone who has used any SQL database knows their internal data types? Although, true, I have seen a few odd table schemas in my

Seriously. Data types -- surely anyone who has used any SQL database knows their internal data types? Although, true, I have seen a few odd table schemas in my time. Mainly in SQL Server databases it has to be said.

meepmorp 15y ago
I've interviewed developers with (claimed) years of database experience who couldn't articulate the difference between char and varchar. Another thing I've noticed more recently - rails developers who know almost nothing about how the database works. Like they might barely be able to write a inner join among 3 tables; left/right joins flummox them entirely. This is by no means universal, but I've encountered it several times in the last year, enough to be a trend in my mind.
- Craiggybear 15y ago
  Aaaahhhhh ... rails developers. I take your point. As to CHAR vs VARCHAR -- well, is it fixed length or constantly changing? The clue is in the name. And CHAR is usually up to 50% faster on overhead and VARCHAR uses padding which can be a huge waste of space. They should just know this. Then, SQL Joins are plain English and should be easily visualised in the abstract (it does help if you can see the data in front of you though, I can't deny). Date vs Time is something I am finding confuses lots of people. Precision as well.
  - Skalman 15y ago
    Regarding CHAR/VARCHAR: At least in MySQL VARCHAR only has one byte of padding, while CHAR is a huge waste of space if you don't use mostly full-length strings. Edit: http://dev.mysql.com/doc/refman/5.6/en/char.html http://dev.mysql.com/doc/refman/5.6/en/char.html
  - Erwin 15y ago
    Well, that's database dependent -- for Postgres there's no real performance difference: http://wiki.postgresql.org/wiki/FAQ#What_is_the_difference_between_the_various_character_types.3F http://wiki.postgresql.org/wiki/FAQ#What_is_the_difference_b... It's CHAR that pads with spaces, not VARCHAR.
- meric 15y ago
  I used to write lots of SQL a couple of years back in university, I even think I was quite good at it because I scored near the top of the class, but since that database class I haven't written many more lines of SQL. These days the ORM takes care of a lot of use cases... I think I still know what a left/right join but I am no longer confident in my knowledge. I think a lot of people my age are similar. We aren't 30 year olds who have had 5 years of actual SQL experience before ORMs got popular. We've had maybe 1 year or so and then after that its all just ORM.
- richardlblair 15y ago
  This makes sense. The models in rails do all the heavy lifting. Unfortunately all too often the developers aren't aware of the abuse they are putting the DB through. Personally I use Django. When it comes time for me to perform a query that might require multiple inner joins, a group by, and aggregates I will often write the query by hand. This allows me to figure out the requirements of the query. From there I figure out how to accomplish that with the ORM. I think more people should take this approach. It would help people understand what it is they are doing with the database. EDIT: That being said, having an ORM do exactly what you want can be like pulling teeth sometimes. I must say, I'm not fully sold on the ORM.
  - meepmorp 15y ago
    Like any tool, ORMs are appropriate for some uses, and not for others. In the Python world, I like SQLAlchemy. It performs well, it's fairly well documented (improving, though I've had to read source code to figure out options before), and it's not tied to a web framework, the way I believe Django's is.
  - jacques_chester 15y ago
    The description I give is that rails programmers think of databases as files with a funny accent. The classic CF in rails is a form that ultimately deals with > 1 table. Multi-table forms are a pain for everyone, but with a bit of database juju you could disguise the complexity with, say, an updateable view.
ams6110 15y ago
My peeve in any flavor of database is people who bastardize some other datatype when a more appropriate one is available, e.g. storing dates as epoch offsets in an INT or BIGINT column rather than just using a DATETIME data type.
jcromartie 15y ago
I've found several columns using floating point to store monetary amounts in the system I'm working on. It makes for some real problems down the line.

meepmorp 15y ago

I've interviewed developers with (claimed) years of database experience who couldn't articulate the difference between char and varchar. Another thing I've noticed more recently - rails developers who know almost nothing about how the database works. Like they might barely be able to write a inner join among 3 tables; left/right joins flummox them entirely. This is by no means universal, but I've encountered it several times in the last year, enough to be a trend in my mind.

Craiggybear 15y ago
Aaaahhhhh ... rails developers. I take your point. As to CHAR vs VARCHAR -- well, is it fixed length or constantly changing? The clue is in the name. And CHAR is usually up to 50% faster on overhead and VARCHAR uses padding which can be a huge waste of space. They should just know this. Then, SQL Joins are plain English and should be easily visualised in the abstract (it does help if you can see the data in front of you though, I can't deny). Date vs Time is something I am finding confuses lots of people. Precision as well.
- Skalman 15y ago
  Regarding CHAR/VARCHAR: At least in MySQL VARCHAR only has one byte of padding, while CHAR is a huge waste of space if you don't use mostly full-length strings. Edit: http://dev.mysql.com/doc/refman/5.6/en/char.html http://dev.mysql.com/doc/refman/5.6/en/char.html
- Erwin 15y ago
  Well, that's database dependent -- for Postgres there's no real performance difference: http://wiki.postgresql.org/wiki/FAQ#What_is_the_difference_between_the_various_character_types.3F http://wiki.postgresql.org/wiki/FAQ#What_is_the_difference_b... It's CHAR that pads with spaces, not VARCHAR.
meric 15y ago
I used to write lots of SQL a couple of years back in university, I even think I was quite good at it because I scored near the top of the class, but since that database class I haven't written many more lines of SQL. These days the ORM takes care of a lot of use cases... I think I still know what a left/right join but I am no longer confident in my knowledge. I think a lot of people my age are similar. We aren't 30 year olds who have had 5 years of actual SQL experience before ORMs got popular. We've had maybe 1 year or so and then after that its all just ORM.
richardlblair 15y ago
This makes sense. The models in rails do all the heavy lifting. Unfortunately all too often the developers aren't aware of the abuse they are putting the DB through. Personally I use Django. When it comes time for me to perform a query that might require multiple inner joins, a group by, and aggregates I will often write the query by hand. This allows me to figure out the requirements of the query. From there I figure out how to accomplish that with the ORM. I think more people should take this approach. It would help people understand what it is they are doing with the database. EDIT: That being said, having an ORM do exactly what you want can be like pulling teeth sometimes. I must say, I'm not fully sold on the ORM.
- meepmorp 15y ago
  Like any tool, ORMs are appropriate for some uses, and not for others. In the Python world, I like SQLAlchemy. It performs well, it's fairly well documented (improving, though I've had to read source code to figure out options before), and it's not tied to a web framework, the way I believe Django's is.
- jacques_chester 15y ago
  The description I give is that rails programmers think of databases as files with a funny accent. The classic CF in rails is a form that ultimately deals with > 1 table. Multi-table forms are a pain for everyone, but with a bit of database juju you could disguise the complexity with, say, an updateable view.

ams6110 15y ago

My peeve in any flavor of database is people who bastardize some other datatype when a more appropriate one is available, e.g. storing dates as epoch offsets in an INT or BIGINT column rather than just using a DATETIME data type.

jcromartie 15y ago

I've found several columns using floating point to store monetary amounts in the system I'm working on. It makes for some real problems down the line.