Databse structure

Village Idiot · 01-13-2009, 09:49 PM

Here is release candidate 1 for the database structure, please report any errors that you may see. I will make this official in a few days. Any changed will likely be minor so do not be afraid to start coding off of this.

sql Code:

-- phpMyAdmin SQL Dump
-- version 2.11.9.4
-- [url]http://www.phpmyadmin.net[/url]
--
-- Host: localhost
-- Generation Time: Dec 29, 2008 at 05:54 PM
-- Server version: 5.0.67
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `justanot_phlox`
--

-- --------------------------------------------------------

--
-- Table structure for table `friends`
--

CREATE TABLE IF NOT EXISTS `phlox_friends` (
  `friend_id` int(9) NOT NULL AUTO_INCREMENT,
  `friend_user_id` int(9) NOT NULL,
  `friend_other_user_id` int(9) NOT NULL,
  `status` int(1) NOT NULL,
  PRIMARY KEY  (`friend_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `friends`
--


-- --------------------------------------------------------

--
-- Table structure for table `gallery`
--

CREATE TABLE IF NOT EXISTS `phlox_gallery` (
  `gallery_id` int(9) NOT NULL AUTO_INCREMENT,
  `gallery_user_id` int(9) NOT NULL,
  `gallery_name` int(9) NOT NULL,
  `gallery_createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`gallery_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery`
--


-- --------------------------------------------------------

--
-- Table structure for table `gallery_comment`
--

CREATE TABLE IF NOT EXISTS `phlox_gallery_comment` (
  `gallery_comment_id` int(9) NOT NULL AUTO_INCREMENT,
  `gallery_comment_user_id` int(9) NOT NULL,
  `gallery_comment_gallery_id` int(9) NOT NULL,
  `gallery_comment_text` text collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`gallery_comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_comment`
--


-- --------------------------------------------------------

--
-- Table structure for table `gallery_rating`
--

CREATE TABLE IF NOT EXISTS `phlox_gallery_rating` (
  `gallery_rating_id` int(9) NOT NULL AUTO_INCREMENT,
  `gallery_rating_user_id` int(9) NOT NULL,
  `gallery_rating_gallery_id` int(9) NOT NULL,
  `gallery_rating_score` int(1) NOT NULL,
  PRIMARY KEY  (`gallery_rating_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_rating`
--


-- --------------------------------------------------------

--
-- Table structure for table `gallery_subscriptions`
--

CREATE TABLE IF NOT EXISTS `phlox_gallery_subscriptions` (
  `galSub_id` int(9) NOT NULL AUTO_INCREMENT,
  `galSub_user_id` int(9) NOT NULL,
  `galSub_gallery_id` int(9) NOT NULL,
  PRIMARY KEY  (`galSub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `gallery_subscriptions`
--


-- --------------------------------------------------------

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `phlox_items` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_user_id` int(11) NOT NULL,
  `item_name` varchar(255) collate latin1_general_ci NOT NULL,
  `item_link` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`item_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `items`
--


-- --------------------------------------------------------

--
-- Table structure for table `item_comments`
--

CREATE TABLE IF NOT EXISTS `phlox_item_comments` (
  `item_comment_id` int(9) NOT NULL AUTO_INCREMENT,
  `item_comment_item_id` int(9) NOT NULL,
  `item_comment_user_id` int(9) NOT NULL,
  `item_comment_text` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`item_comment_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_comments`
--


-- --------------------------------------------------------

--
-- Table structure for table `item_ratings`
--

CREATE TABLE IF NOT EXISTS `phlox_item_ratings` (
  `item_rating_id` int(9) NOT NULL AUTO_INCREMENT,
  `item_rating_user_id` int(9) NOT NULL,
  `item_rating_item_id` int(9) NOT NULL,
  `item_rating_score` int(1) NOT NULL,
  PRIMARY KEY  (`item_rating_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `item_ratings`
--


-- --------------------------------------------------------

--
-- Table structure for table `options`
--

CREATE TABLE IF NOT EXISTS `phlox_options` (
  `option_id` int(9) NOT NULL AUTO_INCREMENT,
  `option_name` int(9) NOT NULL,
  `option_value` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`option_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `options`
--


-- --------------------------------------------------------

--
-- Table structure for table `templates`
--

CREATE TABLE IF NOT EXISTS `phlox_templates` (
  `template_id` int(9) NOT NULL AUTO_INCREMENT,
  `template_name` varchar(255) collate latin1_general_ci NOT NULL,
  `template_dir` varchar(255) collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`template_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `templates`
--


-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `phlox_users` (
  `user_id` int(9) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(255) collate latin1_general_ci NOT NULL,
  `user_pass` varchar(255) collate latin1_general_ci NOT NULL,
  `user_rank` int(9) NOT NULL,
  `user_regdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `users`
--


-- --------------------------------------------------------

--
-- Table structure for table `user_subscriptions`
--

CREATE TABLE IF NOT EXISTS `phlox_user_subscriptions` (
  `userSub_id` int(9) NOT NULL AUTO_INCREMENT,
  `userSub_user_id` int(9) NOT NULL,
  `userSub_target_user_id` int(9) NOT NULL,
  PRIMARY KEY  (`userSub_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

--
-- Dumping data for table `user_subscriptions`
--

Ross · 01-16-2009, 10:16 PM

I mapped out the relationships for this and it looks like there's a missing link between items (I assume media) and galleries. I'll make up a patch in a minute.

Village Idiot · 01-16-2009, 10:23 PM

Items go per gallery, there is no link between them needed.

Salathe · 01-16-2009, 10:31 PM

How does the system know which gallery (or galleries, if you want that to be possible) an individual item belongs in?