4 ms·

It'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

It'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 ago
Please, 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 14y ago
  Sounds 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 14y 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 14y ago
  All 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.

andrewaylett 15y ago

Please, 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 14y ago
Sounds 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 14y 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 14y ago
All 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.