Database design for IDEA Version 0.8 ------------------------------------ 7-April-2003 (there may be some updates, but minor) PEOPLE ------------------------------------------------------- pers_id number primary key pers_handle char(10) used for login pers_first_name varchar2(20) used for greeting pers_last_name varchar2(20) used for formal purposes pers_status number(1) 0 for occupant, 1 for friendly, 2 for unwanted pers_birthdate date used for birthday greeting, see WELCOME pers_location number(1) 0 for outside, 1 for inside pers_prefs custom type to be defined as necessary ------------------------------------------------------- pers_prefs is a custom type so we can add on to it when we decide what preferences need to be stored. Eg. pers_prefs.lights_auto_on, pers_prefs.GUI_theme, etc SCHEDULE ------------------------------------------------------- sch_from_id number corresponds to PEOPLE.pers_id sch_start date ORACLE's date format contains time data sch_end date " " sch_activity varchar2(40) brief description of event sch_notes varchar2(500) additional info, elaboration, etc ------------------------------------------------------- the activities of all users are stored in this table. However, upon logon, only the current user's schedule is displayed, ie. you can only see your own schedule. SCHEDULE.sch_from_id is compared to PEOPLE.pers_id Entries should be deleted after they occur, but this is up to implementation people. =) MESSAGE ------------------------------------------------------- msg_from_id number corresponds to PEOPLE.pers_id msg_to_id number " " msg_time date date/time stamp msg_txt varchar2(500) message text ------------------------------------------------------- Similar to Scheduler. User IDs are checked to show messages to the right person. Message deleted after read. It's really up to the implementation people. If they want to give the end-user a choice, also can. Can add a msg_read field if needed. WELCOME ------------------------------------------------------- wel_id number primary key wel_txt varchar2(500) welcome message text wel_type number(1) 0 for generic, 1 for birthday, 2 for holidays wel_date date only for holidays ------------------------------------------------------- On normal days, a random generic message is displayed. eg "Good $time_of_day, $name!", "Lovely day, isn't it?" On birthdays, a random birthday greeting eg "Happy $age, $name!", "Happy Birthday, and more to come!" On holidays, relevant seasons greetings eg "Merry Christmas!", "Happy New Year!" LOG ------------------------------------------------------- log_time date date/time stamp log_txt varchar2(500) actual log message, can be generated by any function ------------------------------------------------------- JOKE ------------------------------------------------------- joke_id number primary key joke_txt varchar2(500) joke text joke_cat number(1) joke categories. 0 for political, 1 for lightbulbs, etc ------------------------------------------------------- Users can subscribe to preferred categories, storing in prefs. Then only jokes from those categories will be displayed. SAMPLE ------------------------------------------------------- This is a sample message. This paragraph has 500 characters in it. That is the maximum length I have allocated to the different messages stored in the database, which includes: welcome messages, jokes, event logs and user-to-user communication. Do you think 500 is too little? I think it is enough.4567 I’m already having trouble filling up all this space with meaningful sentences. The quick brown fox1 jumps over the lazy dog. The quick brown fox jumps over the lazy dog. Abcdefghijklmnopqrstuvwxyz1231