online_create's Journal

This is an idea I've had, that I'm developing. However, all are welcome to join in along the way. The more help I can get, the better

The project is outlined below

The chosen database - MySQL
The chosen language - Perl

My idea, a creativness server. Sounds weird, I know. Here's the URL ideas

http://www.domain.com/
- Welcome screen, New user sign up, login, manage account etc
http://public.domain.com/
- Welcome screen, New user sign up, login, manage account etc
http://my.domain.com/
- Welcome screen, New user sign up, login, manage account etc

http://my.domain.com/$type/
- Only availabe once logged on (Otherwise prompt for logon)
- Allows $user to edit $entries of $type (Type being, type of submission - Poem, Story etc)

http://public.domain.com/$type/
- Allows searching of $entries that match $type, as long as $entry is public/available

http://public.domain.com/$type/$ID
- $user's entry ($ID) of $type (Viewable only if security allows, otherwise prompts for logon)

http://my.domain.com/$type/$ID
- Allow editing of $user's entry ($ID) if logged on and the person who made the entry, else just show entry(If security allows)

http://public.domain.com/$user
- View all of $user's entries (Publically viewable ones, or security setting permitting)

http://public.domain.com/$user/$type
- View all of $user's entries of $type (Publically viewable ones, or security setting permitting)


$type is defined by, essentially, me (It will be gathered from the 'types' table, once populated). Hence my 'How are you creative' poll (http://www.livejournal.com/poll/?id=50428)
The only three $type's I can currently think of are 'Poems' 'Stories' and 'Lyrics'. You may think that images are creative - And they are. But, for that, go here http://www.livejournal.com/users/fotobilder

So, I sign up to this website, and decide to submit a few short stories. But, that's all I submit
I log on and goto http://my.domain.com/stories/ and I can edit my submissions. If however, I goto http://my.domain.com/poems/, I'll be slapped and told I haven't submitted any poems yet

Depending on how complex http://public.domain.com/$user is to generate, it may be down to the user type (E.G. you've got more priv.'s if you've submitted a lot of work, or whatever)
Generally people will link to stuff by http://public.domain.com/$type/$ID though, I'd imagine

The complex bit for me, if the design of the database. I've never designed a database before, so it could be tricky
;-)

The general idea for the database goes thusly

- Users table
*UserID - Auto incrementing number, thus unique. Something that's not really relevent, but can be used simply to find out how many people have joined up
*Username - A unique name, keyfield (Simlar to a username on LJ, TMTL for instance)
*Realname - The users real name (No way to confirm, obv). This'd be for e-mailing them, I'd guess
*Password - The users password, maybe hashed. I'm not sure. Hashing it would create issues with lost passwords, as the only hashes I know are one way ones
*Account_type - Just for scalability. Not currently used, though (Account type of x can\cannot perform action y)
*E-Mail - So the user can be contacted
*DOB - Just because it seems right
*Random_confirmation_junk - Just a few randomly picked chars. so the user can enter them on a form (Once only) to confirm the valid e-mail address. Account isn't usable until e-mail address is confirmed
*Confirmed - Has the 'Random_confirmation_junk' been confirmed? If yes, flag account as confirmed (And therefore active)
*Deleted - Has this account been deleted? I wouldn't actually remove stuff, just disable it

The only stuff from this table that'll be public is the username (so people can goto http://public.domain.com/$user or search on stuff, and provide a user as a result etc etc)

- Types table
*TypeID - Auto incrementing number, thus unique
*Typename - For instance 'stories' and 'lyrics' are two valid entries for instance

Typename would be public, so people knew what they could submit

- TypeEntriesX table(s)
Multipul tables with the name TypeEntriesTypeID. For instance TypeEntries1 would be all the entries for 'stroies' (Assuming that TypeID 1 was for 'stories')
The contents would all be the same, though

*EntryID - Auto incrementing number, thus unique. This would be referenced via http://public.domain.com/$type/$ID (Assuming public entry)
*UserID - The user id from the Users table (Taken the ID, as they can change their name, if they want to), so you know who's submitted what
*Security - This is simply (For now) 0, 1 or 2. 0 = Private entry (But would it be needed?). 1 = Only viewable to logged in users and 2 = public access (The default one)
*Title - Not required, but entered if the entry has a title (Useful only for lyrics, I thought)
*Entry - The actual body of the entry
*Written_by - May sound daft, but not every submitted would be written by the person who submitted it
*Deleted - Has this entry ($ID) of $type been deleted? If so, flag it as so. Just disable it though, don't actually delete anything
*Allow_comments - A simple 0 or 1. Not sure if this is to be implemented, as I'm not sure how to tackle comments, yet. If comments are something that'd be implemented, then...
*Num_comments - Number of comments received for this $type$ID

This is where you come in
- The visuals. I can never make things visually impressive. I hope you can!
-The technicals. As I say, I've never designed a database before, so I could be waaaay off with my ideas for it. I have therefore never connected to a database via Perl either