Eugh, many better ways.
CREATE TABLE <table_name> (
uid int not null,
gid int not null,
primary key (uid, gid)
);
The junction table's primary key is a composite of the usr and grp tables'
foreign keys. If your dbms supports foreign key contraints set it for
cascading update and delete (of PK), etc.
VERY inoften will you ever store a composite field (ie. list in a field) at
the lowest level of your database design.
With this structure you can find what groups a user belongs to by a simple
"SELECT gid FROM <table_name> WHERE uid = <var>", or how many users are in a
group by "SELECT count(uid) AS cnt WHERE gid = <var> GROUP BY gid", etc.
If that data was stored in a list in a field, it would negate the purpose of
storing it in a database.
-----Original Message-----
From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
Sent: May 6, 2001 09:38
To: CF-Talk
Subject: Quick DB Design
I'm trying to normalize a table to best hold the following data:
username, group1, group2, group3, group4... groupN...
there are 1000s of unique usernames, and 10000s of unique groups
every user can subscribe to 1-10000 diffrent groups (so I don't know how
much group colums to put).
I need somehow to maintain a list of subscribed groups for each user.
is there a better way then 2 fields like the following:
username (nvarchar(30)
groups (longtext) = > group1,group3,group15,group7,group9 ?
Thanks,
-=Michael.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
