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
