On Fri, Dec 10, 2004 at 06:15:50PM -0800, Eric Brown wrote: > Option 1: > create table a (id serial, hosts text[]); > > OR > > Option 2: > create table a (id serial); > create table hosts (id int references a, host text);
Option 2 will save a lot of developer & query time, as it's much more standard. If you need a VIEW like table a, it's easy to do like this: CREATE VIEW view_a AS SELECT a.id, ARRAY(SELECT host FROM hosts WHERE id = a.id) FROM a; > Table 'a' will have about 500,000 records. There will probably be > about 20 reads for every write. Each id has approximately 1.1 hosts. > If I use the array (option 1), I'll have to loop over the elements > of the array to see if I have a match when querying a given id. This > isn't hard, but it means that SELECT will always return 1 record > when, in option 2, it might return 0 records and only have accessed > the indexes. > > Given the indexes that will be built and disk pages used (cached or > otherwise), which mechanism would be faster for searching. It's a lot easier to search under option 2, and besides, speed isn't everything ;) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
