1
\$\begingroup\$

I wish to create a database for a webpage where users are able to add their own events to a timetable. Users will be able to decide if the events should be recurring (weekly) or not. Users should be able to query other groups of users to organize a time for a meeting. That is it.

What I have so far is as follows:

Users (
  userID INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(80),
  PRIMARY KEY (user_id)
);

Groups {
  GroupID INT NOT NULL AUTO_INCREMENT,
  GroupName NVARCHAR(100)
  private BOOLEAN
}

Membership {
  UserID (FOREIGN KEY, UNIQUE),
  GroupID( FOREIGN KEY, UNIQUE)
 }


Events (
    event_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(80) NOT NULL,
    description VARCHAR(200),
    start_time DATETIME,
    end_time DATETIME,
    group_id INT NOT NULL,
    recurring BOOLEAN
);

Group (
    group_id INT NOT NULL,
    user_id INT NOT NULL
);

When a user wants to arrange a meeting using my database, I will be forced to use 3 queries and 2 loops, is there a better way than this? It all seems extremely complicated.

int i = 0;
String[] names = { Request.Form["usernames"]Split(' ') }; //retrieving names from form
List<int> user_ids = new List<int>();
foreach(string name in names){ 
    int user_id = db.QueryValue("SELECT user_id FROM users WHERE username = name");
    user_ids.Add(user_id); //now I have a list of all user_ids
}
db.Execute("INSERT INTO group(groupName) values(Request.Form["Group name"])");
int groupID = db.QueryValue("SELECT groupid from group where            groupname="Request.Form["Group name "]");
foreach (string user_id in user_ids) {
      db.Execute("INSERT INTO membership(userid,groupid) values(user_id, groupID);"
}
\$\endgroup\$
  • 2
    \$\begingroup\$ This should probably be moved to Code Review... \$\endgroup\$ – Trevor Pilley Mar 8 '12 at 22:32
  • \$\begingroup\$ Do you mean you will create a new group for every event? If so, why not have a EventUsers table instead of groups and memberships? Also, if you change recurring to an int, positive values can imply number of days between events while -1 can mean monthly, -2 can mean every second months and so on. More dynamic and allows for yearly recurrances \$\endgroup\$ – Mattias Åslund Mar 8 '12 at 22:40
  • \$\begingroup\$ No, I will not create a new group for every event. I will allow the users to choose from existing groups or to create their own group! Many thanks for the suggestion about recurrences, I will bear that in mind. Any other thoughts? \$\endgroup\$ – Simon Kiely Mar 8 '12 at 22:50
  • \$\begingroup\$ Ok, but if I create a group and create events for that ten times and then add a user to the group, she will seem to have been in those first events too. Thus you still need the EventUsers table to track the group members for the actual event \$\endgroup\$ – Mattias Åslund Mar 8 '12 at 22:58
4
\$\begingroup\$

From looking at your database description . . .

  • No unique constraint on Users.username. You can have 1000 users named "Fred".
  • No unique constraint on Groups.groupname. You can have 1000 groups named "Hamburger". You probably don't want to allow that.
  • If you intend to build Membership like UserID integer not null unique references Users (userID), and GroupID integer not null unique references Groups (GroupID) then each user and each group can appear in that table only once. That means that if I have membership in the group Hamburger, you cannot also have membership in the group Hamburger. I think you probably want to drop the UNIQUE constraint from both those clauses, and add PRIMARY KEY (UserID, GroupID).
  • It's not clear what the difference is between the two tables Membership and Group.

From looking at your code . . .

  • db.QueryValue("SELECT user_id FROM users WHERE username = name"); You're selecting user identifiers given the users' names. But names aren't unique in your database. If I select one "Fred", and there are 1000 Freds in your database, I'm going to end up with 1000 different user identifiers. You probably don't want to do this.
  • db.Execute("INSERT INTO group(groupName) There's no column named "groupname" in that table.
  • int groupID = db.QueryValue("SELECT groupid from group where groupname = You're selecting group identifiers given the group's name. Same problem as with selecting user identifiers above.

Unless I misunderstand you, and that's always possible, you should be able to insert existing users and existing groups into Memberships with a single INSERT statement.

| improve this answer | |
\$\endgroup\$
0
\$\begingroup\$

To handle the complexity issue create business objects. For example, a User class that has an Events collection. That is, classes that reflect the real world relationships you're modeling.

Think of a tiered code structure here. Your client object(s) (i.e. layer) manipulates your business object(s) (i.e. layer). DataAccess objects (i.e. layer) moves data between the business objects and your database using the Database functionality.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy