Reflections:
This week in Phase 0 , I shift gear from learning the Ruby language to database and SQL. I am working on building data tables and learning about allocating appropriate data type to each field.
I was giving a raw table such as this:
and I created a xml version with socrates with an SQL Designer which looks like this:
The exercise is deceptively simple, but here are some points for consideration:
This table is not very PC. There are more than 2 genders. Of course, if we define more than this poses challenges to defining which type. Should we store it as an Int (1= Male, 2 = Female, 3 = Undefined, 5 = I find this offensive.) I like 5. Or perhaps char(1), for m as male, f as female, and o as other.
- This amusing dilemma is discussed on http://stackoverflow.com/questions/4175878/storing-sex-gender-in-database This amusing dilemma is discussed on http://stackoverflow.com/questions/4175878/storing-sex-gender-in-database
- To account for international numbers, I use varchar(15) for the phone field
- Question: The number inputs are very messy, how do I standardize all of the entires to have a specific format. for example: 1-xxx-xxx-xxxx-oooo (where the oooo are optional)
- When adding a new field, I was confused about VARCHAR vs TEXT as a type for string inputs such as first_name. but this helps:
- TEXT is good:
- If you need to store large texts in your database If you do not search on the value of the column If you select this column rarely and do not join on it.
- VARCHAR is good:
- If you store little strings If you search on the string value If you always select it or use it in joins.
- What about CHAR vs VARCHAR? “Generally pick CHAR if all rows will have close to the same length. Pick VARCHAR when the length varies significantly. CHAR may also be a bit faster because all the rows are of the same length. (within two characters length difference).”
I assume first and last names will have varying length, so VARCHAR is a better choice. When in doubt, use VARCHAR.
Source: