On Jul 31, 1:19 pm, Andrew Pace <andrewpp...@gmail.com> wrote: > Thanks for your feedback. > > It is possible to ensure certain formats for the text column prior to > saving it to a database.
Yes, but that puts all the type-checking in the application layer and defeats one of the big advantages of using a database instead of a flat text file. > This makes querying this data quite easy in > fact. Just because the user entered in a form like 2/12/1980, doesn't > mean it has to get stored in that fashion....I would hope that is > obvious, as this should be a concern no matter how you structure your > database. A simple validation check solves that one. > No it doesn't. 2/12/1980 is ambiguous no matter how much validation you throw at it. > Finding a value is then as simple as: select all from users_profile > where profile_key = "birthday" and profile value =,>,< "whatever". > Not too difficult. No. > and < don't work on dates stored as strings, which means you need a date parsing function. IIRC, this is generally not present in SQL, which means you have to involve the application layer to complete the query, which is a performance and architectural problem. If you had a date field in the DB for birthday, this query would be absolutely trivial and would be possible in the DB alone. > > Here is an SQL statement that works perfectly: > $sql = 'SELECT * FROM `users_profile` WHERE profile_key = \'email > \' and profile_value = \'andrewpp...@gmail.com\'; > > Another example. > $sql = 'SELECT * FROM `users_profile` WHERE profile_key = \'zipcode > \' and profile_value > \'70000\'; > > The data is not mangled at all, Yes it is. If the data is numeric or another non-text type, then it is mangled by being declared as text. There's a difference between "4" and the number 4, and between "10/10/1974" and the date of 10 October 1974. > and is still easily retrievable. Only for the simplest queries. This sort of schema effectively prevents you from harnessing the power of the database for complex queries, because the database cannot reason about the different fields that you are using (since they're all stored as if they're the same field). > It > is, however, very flexible. I accept that searching through all text > fields can be slower from a performance standpoint than using other > types of fields. Yes, that's one problem -- even if you index the field, the index will take much longer to search than necessary. There is a more fundamental problem, though, and that's that the "data model" you have simply does not model the data -- for example, it represents text, numbers, and dates all as if they were text, and it blurs the difference between fields. There is more to good database design than simply providing enough space to store the data. Really, I know this may look simpler, but the fact is that it is a bad idea for most use cases, and it does not scale past the most trivial queries. I am hard put to think of a single good reason for the design you describe. Just don't do it. > > Andrew Best, -- Marnen Laibow-Koser http://www.marnen.org mar...@marnen.org --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk@googlegroups.com To unsubscribe from this group, send email to rubyonrails-talk+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
