I created an account for our team over at funpic.org, and a group email account since it wanted an email address. Also, our team is officially female, at least to the people over over at funpic.
The database is kinda sorta set up. Please note that I know next to nothing about efficient database design.
items table - the main table with all the bookmarked items in it.
Structure:
itemid - int(10) - UNSIGNED - Not null - Auto increment
url - varchar(255)
user - int(10) - UNSIGNED - Not null - Default: 0
usertype - enum('person', 'group') - Not null - Default: person
media - blob - BINARY
thumbnail - blob - BINARY
mediatype - varchar(10)
title - varchar(255)
description - text
comment - text
tags - text
creation - date - Not null - Default: 0000-00-00
expiration - date - Default: 0000-00-00Most things should be self-explanatory, but some things should be explained:
Indices for the table are url, user, usertype, title, creation, expiration, mediatype. These are the things I suspect will be searched by most often, so this should speed things up if the database gets very large. Itemid is the primary key.
I figured it was ok for most of the things in the table to be null, since you shouldn't have to set all of them for everything--e.g. you shouldn't need to set an expiration date if you don't want something to expire. Things that can't be: itemid, so a specific item can be found again later, user and usertype, so the bookmark (or whatever we're calling these things?) can be associated with a person or group space (it's kinda useless if no one owns it, no?), and creation date, since there's no reason this couldn't be created automatically upon entry into the database.
Itemid is the primary key. Items will generally be selected by user, but to edit an item it will probably be easiest to use an identification number, rather than selecting by user and url or something--especially since a url may not necessarily be required for an entry(???).
User is a 10-digit identification number that can be associated with either a user or a group workspace. It's a fixed-length number, rather than a varchar name (e.g. username or group name) because 1)the internet tells me it will work faster this way; and 2)a group won't necessarily have a name(???). The number can be looked up in the users or groups table, depending on what usertype is--or the other way around of course; actually, that's the more probable case: someone requests something that belongs to them or that belongs to a group they have access to, their number or the group number is looked up, then a second query looks up in the items table where user = their number or the group number and usertype = person or group, depending.
Mediatype needs to change from varchar to an enum, with a list of acceptable file types. I'll figure this out when I start doing the php code to deal with those.
Description is a text entry, perhaps a clip from a website. Comment will be the user's comment on it. Or the user could just use one or the other for both.
Currently the tags go in a text entry. This means each tag needs to be separated from each other tag by a comma or some other delimeter. This may not be an acceptable solution, especially since text entries cannot be indexed--meaning very very slow searches. Then again, if all entries that someone has access to are pulled once and the tags are put in some other format to be accessed by javascript, this may work quite well. I really do need to learn more about relational databases.
users table - the table of user names and passwords. (Eeek, I'm gonna have to figure out sessions in php, aren't I?)
Structure:
userid - int(10) - UNSIGNED - Not null - Auto increment
username - varchar(25) - Not null
password - varchar(25) - Not null
email - varchar(50) - Not nullUsername and email are indices, userid is the primary key. It seems kinda silly to index password, since it seems highly unlikely that I would search by password and not one of the other two columns.
Mostly the only reason email is there is so if people can get their passwords they can retrieve it. There could be fields for things like address, phone number, and whatever else that sites ask you when you sign up for something, but I don't think we actually care about any of those, do we?
There may eventually be other columns with various preferences, but since we haven't decided what those will be yet they aren't in there.
groups table - the table of groups, group descriptions, and who has access.
Structure:
groupid - int(10) - UNSIGNED - Not null - Auto increment
user - int(10) - UNSIGNED - Not null - Default: 0
usertype - enum('person', 'group') - Not null - Default: person
media - blob - BINARY
thumbnail - blob - BINARY
mediatype - varchar(10)
title - varchar(255)
description - text
comment - text
tags - text
creation - date - Not null - Default: 0000-00-00
expiration - date - Default: 0000-00-00This table is pretty much the same as the items table, since one of the ideas is that someone should be able to treat a group they have access to the same way they would treat an individual item. Hence the media, tags, comments, etc. columns. The indices are the same also, with the exception of url, which is not in this table.
Groupid is also treated as an index, not a primary key, because each group will be in the table more than once. This allows each user with access to it to create their own personal tags, comments, etc. that aren't shared by the other members. When a group is created all users will have those fields set to whatever the group creator puts in them, but the other users can modify those thereafter. Theoretically there could also be comments, etc. visible to the entire group, which would be stored in a row where the group is owned by itself--i.e. the usertype would be "group" and the user would be the same as the groupid.
This brings up a second point: a group can be accessed by users, but also by other groups. So you could have two groups where all the members also have access to a third group workspace. I'm not really sure what this buys you--maybe subcommittee type stuff?--but it's pretty easy to implement and might be useful, so why not?
And that's the database, thus far.
Some final comments:Group space versus individual space
Because of the way the items table is set up, there is no natural way for an item owned by a group to be linked to another part of an individual's workspace. Well, I guess tagging might do it, but I haven't figured out quite how. The more likely case is that there would be two entries in the database, one belonging to the group and another belonging to the user, with a checkbox or something in the interface that says to update the other copy. Then the php update function will be called multiple times, once for the one you're actually changing and again for the linked item. I don't yet know how to link between the two--tagging might come in here, too.
Multiple users and threading
More than one person can be working in a group workspace at once, even editing the same item. How do we maintain that item's integrity? Some sort of javascript refresh function will help, but if two people are editing the same item concurrently and hit submit several seconds apart, how do we keep from losing the changes created by one without ruining both?
Next stop, PHP!