Hi Rob, thanks for your reply.
> -----Original Message----- > From: Rob [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 10, 2002 2:57 PM > To: Peter Romianowski; [EMAIL PROTECTED] > Subject: Re: "dynamic table width" > > > As usual, there are several different approaches, each with different > advantages and disadvantages. > > The simplest approach is just to put all the "optional" fields in the main > table, and each user can decide how many of them they can be bothered to > fill in. The disadvantages here are that there is potentially a lot of > wasted storage, and adding new "optional" fields is tough- you've got to > change the schema for your main table. I pretty much like this aproach. I have a set of 30 standard properties. If there is a customer who wants more / other fields, *then* I would add another table for her. This should be the case for a handful of customers only. > The most flexible approach is to abandon type safety altogether and use a > 'property list' table with 'name' and 'value' fields, along with the primary > key of the entry in the main table. Then users can create any set of > "optional" fields they want, and there is no administrative maintenence > burden. The disadvantages, of course, are that you'll probably make the > field values be 'text' or something, so applying spiffy date or numeric > operations to them is not so simple, and that the abundance of JOINs > necessary to replicate the original layout, while not necessarily hurting > asymptotic complexity, can definitely slow down your database ops. I think this one is not usable for me (although it's the clearest way). This would be far too heavy for the #rows I expect. So I think using "version 1" is a good trade-off between space and performance. Thanks, Peter > -rob > > On 10/6/02 at 2:25 pm, Peter Romianowski <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > I got a table with standard userdata (email, address). I want > > to be able to provide additional fields (like age, gender etc). > > Generally I have about 8 standard fields and up to 30 optional > > fields which may vary. Say something like this: > > > > Customer A wants its users to provide email, address and gender. > > Customer B email, address, age, shopping-preferences. > > > > Now Customer A has a million users and Customer B 2 million. And > > there will be a Customer C..<something> too :) > > > > I wonder what would be the best way to achieve this. I will have > > to handle a huge number of users in that table. My first guess would > > be to create a table with 40 columns where most columns are most > > of the time empty. Does this have an impact on the performance? > > I cannot predict which customer will use which fields since they > > should be totally free in their selection. I cannot predict the > > number of customers either. > > > > Is using a single table the best choice? (I know we learned not to > > do so at university - but hey, this is real life... :) > > > > Thanks, > > Peter > > > > magic words: sql, query > > > > --------------------------------------------------------------------- > > Before posting, please check: > > http://www.mysql.com/manual.php (the manual) > > http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
