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);"
}
