The PRATTLES Table:
| Column |
Data Type |
Extra info |
| prattleId |
INTEGER UNSIGNED NOT NULL AUTO_INCREMENT |
Primary Key |
| pratId |
INTEGER UNSIGNED NOT NULL |
|
| prattle |
VARCHAR(150) NOT NULL |
|
| prattleDate |
DATETIME NOT NULL |
|
CREATE TABLE `prattles` (
`prattleId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`pratId` VARCHAR(150) NOT NULL ,
`prattle` VARCHAR(150) NOT NULL ,
`prattleDate` DATETIME NOT NULL ,
PRIMARY KEY ( `prattleId` ),
FOREIGN KEY (`pratId`) REFERENCES prats(`pratId`)
);
notes: Each time a new prattle is added, the current_datestamp will be added automatically to the prattleDate column. |
The PRATS Table:
| Column |
Data Type |
Extra info |
| pratId |
INTEGER UNSIGNED NOT NULL AUTO_INCREMENT |
Primary Key |
| name |
VARCHAR(50) NOT NULL |
UNIQUE |
| password |
VARCHAR(20) NOT NULL |
stored using MD5 encryption |
| bio |
VARCHAR(150) |
|
| homepage |
VARCHAR(100) |
|
| icon |
VARCHAR(200) |
image url |
CREATE TABLE `prats` (
`pratId` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(50) NOT NULL ,
`password` VARCHAR(20) NOT NULL ,
`bio` VARCHAR(150) ,
`homepage` VARCHAR(100) ,
`icon` VARCHAR(200) ,
PRIMARY KEY (`pratId`) ,
UNIQUE (`name`)
);
notes: I decided no 2 prats should be allowed to have the same name, which in retrospect is a no-brainer |
The SYCOPHANTS Table:
| Column |
Data Type |
Extra info |
| pratId |
INTEGER UNSIGNED NOT NULL |
Primary Key. Foreign Key references pratId in PRATT |
| follower |
INTEGER UNSIGNED NOT NULL |
Primary Key. Foreign Key references pratId in PRATT |
CREATE TABLE `sycophants` (
`pratId` INT UNSIGNED NOT NULL ,
`follower` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`pratId`,`follower`) ,
FOREIGN KEY (`pratId`) REFERENCES prats(`pratId`) ,
FOREIGN KEY (`follower`) REFERENCES prats(`pratId`)
);
notes: both pratId and follower are both ids from the prats table. We will use this table to manage who follows who and also to construct the live feed of followed prats |
