What don't you like about the one I proposed yesterday?
-Eric
I would suggest a database schema such as the following (let me know if my HTML table doesn't come across correctly):
Contact ID User ID Field Label Value # # <vcard-ish field name> <vcard-ish extra property> <vcard value> 1 1 FN Ø Full Name 1 1 Home fname@site.domain 1 1 Work full.name@company.com 1 1 TEL Fax +1-011-555-1234 2 1 FN Ø Someone Else 2 1 Home someone@somewhere.de 2 1 TEL Work +1-234-555-6666 This would make it easily mappable to vCard contents without being too locked into vCard specifics. The "Field" column keeps you from having "n" columns for "n" bits of information (and saves space if only a subset of the fields are provided for a contact). The labels would map reasonably well to vCard parameters, while maintaining semantic consistency with tools like Apple's Address Book. Primary Key could be a simple surrogate key assigned to each row, or a compound key consisting of (Contact ID, Field, Label). The compound key would be more intuitive, but may cause implementation problems if the database provider doesn't like NULL values in the PK. Indexes could be built on any combination of columns to optimize searching.
What do you think?
-Eric
On Fri, 11 Aug 2006 16:22:30 -0300, "Michel Moreira" wrote:
How about having an vcard_info table? I really want to go relational :P
2006/8/11, Chuck, Charlie and Charles :
> Michel Moreira wrote:
> > My idea of the db structure. Sorry about the other message.
> >
> >> > I think that can be created 2 tables, one with the contact info type
> >> > and one that has an 1-n relationship between the contacts table and
> >> > tis contact info type table, where u can put the info.
> >> >
> >> > Then u can search freely withouth performance issues and store and
> >> > extends all the contact info without having too many empty fields.
> >>
>
> I've gone down that road, and it ain't fun. The problem is that it
> forces the same display and type on every field, so you can't have a
> little field for state abbreviation, for instance, and forget about
> checkboxes (for primary address, eg). Sorting becomes a nightmare, etc.
> I don't see the huge downside to the big honkin' table of contact
> information. I really don't see storage size as a big downside,
> especially compared to cpu cycles. If you really want to get relational
> you could create sub tables for multiple addresses, phone numbers and
> e-mail addresses, but that's as far as I would go.
>
> -Charles
>
>
>>