[Q] Database design

[Eric]

I have what should be a fairly simple design question.

I usage of mySQL will revolve around a common group + user system. There
can be multiple groups and each group will contain some subset of users.

Each group will have a custom set of data whose values vary per user.

So basically a sample structure might look like this:
(some details intentionally left out)

Database

Table_Group_A
# of user columns

Table_Group_A_UserX
Column 1 Data
Column 2 Data
...
Column N Data

One probably incorrect thought on my part is that it would not be
necessary to store the usernames in Table_Group_A of those users who
belong to that group. But, thinking about it more, it seems like a good
idea. My original intent was to simply look for tables named
Table_Group_A_* and extract the username from the table name...

Does anyone have any recommends concerning this kind of design? I would
like to be able to lay things out in mySQL as cleanly as possible.

[Bill Karwin]

Eric wrote:

One probably incorrect thought on my part is that it would not be
necessary to store the usernames in Table_Group_A of those users who
belong to that group. But, thinking about it more, it seems like a good
idea. My original intent was to simply look for tables named
Table_Group_A_* and extract the username from the table name...

I wouldn't go for this solution, because it's not possible to perform a
JOIN based on part of the table name. You'd have to construct the query
in application code every time. And it's not convenient to get a list
of MySQL tables in application code; there's no "system table" as there
is in some RDBMS products.

Also, I'm not sure how you're intending to make queries to get the
information about users, but I would assume that a typical query would
want a tabular result of information about all the users in a given
group, or even all users from all groups.

My usual take on this is to put the custom values as rows in an
additional table.

CREATE TABLE group (
group_id INTEGER NOT NULL AUTO_INCREMENT,
group_name VARCHAR(50) NOT NULL
);

CREATE TABLE group_attribute (
group_id INTEGER NOT NULL REFERENCES group,
attribute_id INTEGER NOT NULL AUTO_INCREMENT,
attribute_name VARCHAR(50) NOT NULL
);

CREATE TABLE user_attribute_value (
user_id INTEGER NOT NULL REFERENCES user,
attribute_id INTEGER NOT NULL REFERENCES group_attribute
);

CREATE TABLE group_membership (
group_id INTEGER NOT NULL REFERENCES group,
user_id INTEGER NOT NULL REFERENCES user
);

CREATE TABLE user (
user_id INTEGER NOT NULL AUTO_INCREMENT,
user_name VARCHAR(50) NOT NULL
);

Now you could form a query that would return all your users for a given
group with all the values associated with that user in one query:

SELECT u.user_name, a.attribute_name, v.attribute_value
FROM user AS u
INNER JOIN group_membership AS m ON (u.user_id = m.user_id)
INNER JOIN group AS g ON (g.group_id = m.group_id)
INNER JOIN group_attribute AS a ON (g.group_id = a.group_id)
LEFT OUTER JOIN user_attribute_value AS V ON (a.attribute_id =
v.attribute_id)
WHERE g.group_name = ?

Note the outer join, which allows you to get the full list of attributes
that are _supposed_ to be present for a user in that group, even if the
data for that user hasn't been fully entered yet (i.e. there are missing
values).

Also note that this design that I outline above does not account for
some attributes which may be common to all groups. You could put these
attributes in the group tables, or you could make another table listing
all unique attributes, and then the group_attribute table becomes a
many-to-many relation between the groups and the attributes.

Regards,
Bill K.