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. If you know exactly which fields each user will use, you can provide each user their own table with its own schema. When that user views the data, you join the main table with their custom table, and when you use whatever user-independent administrative data management tools you've put together you just look at the main table. The disadvantage here is that you're got to create a new table for every user, leaving an 'open' database schema, which is a real maintenance burden. If one particular user comes up with a new optional field, however, you've only got to change their custom table, which in some situations is a major advantage over the previous method (if security is important or the main table is so large that the alter command is a very hefty op you'd like to avoid). I'm not sure this is right for you if you think you'll have more than a handful of users, although there are a great many variations on this theme (one or a small number of 'secondary' tables) that might be appropriate. 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. -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