3 ms·
Two points: - using pg'a arrays is using SQL against its grain. If you need multiple phone records per user just create a user_data table with three columns (u
by gbog 15y ago
Two points:
- using pg'a arrays is using SQL against its grain. If you need multiple phone records per user just create a user_data table with three columns (user, key, value)
- the number one reason to prefer PostgreSQL over MySQL is just correctness. Every second query I write in MySQL get a wrong answer.
- empthought 15y agoIt's not true that using arrays is using SQL against its grain. If this is an arbitrary list of phone numbers then an array is fine -- the domain being modeled is "list of phone numbers" not "phone number." If you need to query specific phone numbers, then you should have separate columns whose names describe their roles (e.g. cell_phone_number) rather than the EAV monstrosity you proposed. :)
- andrewaylett 15y agoPlease, no -- I really dislike it when an application decrees that I Know What Types Of Phone You Can Have and You Shall Have Only One Phone Number of Each Type. CiviCRM has an interesting scheme too: it lets you say that a number is a [Home|Work|Other|Whatever] [Mobile|Fixed|VoIP|Whatever] number.
- empthought 15y agoSounds like an array of custom data types with phone_number, phone_number_label columns, then. PostgreSQL can do that. I think applications overspecify things like cell_phone_number because they would like to query for all cell phone numbers, and because they're stuck in the obsolete mindset that 1NF can't include arrays.
- gbog 15y ago> rather than the EAV monstrosity you proposed. :) I would really like to know what is monstrous in the "Entity–attribute–value" model, I scanned the Wikipedia article without finding any obvious problems. I had, however, many many problems with separate columns for each values: I have to handle tables with over 600 columns, for example "fixed_phone", "fixed_phone_modified", "fixed_phone_modified_by", "cell_phone", "cell_phone_modified", "cell_phone_modified_by", etc. With an EAV variant, I'll have "entity", "attribute", "value", "modified", "modified_by", "deleted", and it will be much cleaner, or prove me wrong.
- empthought 15y agoAll of those modification times/users should be in a separate audit trail table, which likely will look like an EAV table. That's OK because it's tracking the history of updates, e.g. (time, table_name, column_name, old_value, new_value, changed_by). The current (rather than historical) values should not be in generic EAV tables because putting data of different domains in the same column subverts both the relational model and the DBMS's indexing capabilities. If the point is to label telephone numbers with a role, then a person_id, phone_number, phone_number_label table should be fine.
- pvh 15y agoCounterpoints: - using pg arrays is awesome, and for many access patterns is more performant and equally normalized - MySQL is mostly quite correct, though I disagree with some of the implicit casting of values to integers and some nuances of GROUP BY. That said, operationally speaking, I'd say MySQL replication is less good and can get out of sync in awful ways, there are fewer awesome features (I made some slides: http://love-your-database.heroku.com/ http://love-your-database.heroku.com/), and the storage engines mean you have to choose between useful features and like, not losing your data, man.
- gbog 15y ago> some nuances of GROUP BY I won't call them nuances. SQL tries hard to be very close to set theory, and not just for the sake of it. In set theory, you have no nuances, you have only one possible answer to a question, and MySQL is like a bad student how do not understand why, in maths, you should never affirm something that is not 100% sure and proven.