473,322 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

[Q] Database design

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.

Jul 23 '05 #1
1 1474
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.
Jul 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rushikesh | last post by:
I am designing a WEB BASED Accounting Software with ASP and SQL Server. For this I need some help for the Database design. My design is as follows. I)User table: User_id, UserName..... Users...
5
by: Don Vaillancourt | last post by:
Hello all, Over the years as I design more database schemas the more I come up with patterns in database design. The more patterns I recognize the more I want to try to design some kind of...
1
by: Lane Beneke | last post by:
All, New to the list and a relative newbie to PostgreSQL. Please forgive stupid questions. Designing an application server for a work order processing (et al) database. I have a good handle...
5
by: trynittee | last post by:
Hello, It's been a while since I've posted. I am an intermediate user of Access. I can read simple VB code, have done complex queries, comfortable with event procedures, designing forms and...
12
by: nyathancha | last post by:
Hi, I have a question regarding best practices in database design. In a relational database, is it wise/necessary to sometimes create tables that are not related to other tables through a...
3
by: vicky | last post by:
Hi All, Can u please suggest me some books for relational database design or database modelling(Knowledgeable yet simple) i.e. from which we could learn database relationships(one to many,many to...
0
by: Laurynn | last post by:
# (ebook - pdf) - programming - mysql - php database applicati # (Ebook - Pdf)Learnkey How To Design A Database - Sql And Crystal Report # (ebook-pdf) E F Codd - Extending the Database Relational...
1
by: abhijitbkulkarni | last post by:
Hello, I am designing a .NET database application that uses 3 tier architecture. Starting initially, this application will be desktop application but I will convert it into a website later but...
0
by: sam | last post by:
Hi, Hope you are doing well !!!! One of our clients is looking to augment their team with “Database Architect – DB2" please find below the details and respond with
2
by: programmerx101 | last post by:
Ok, I'm looking for expert advice on this one. I have a database which keeps going into read_only mode. Sometimes it goes into read_only / single user mode. Once it was taken offline completely....
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.