DATABASE SCHEMA HELP

[hamza]

I have 4 tables

mr_mobiles        ----  contain cell/mobile phones information

mr_comments    ---- contains comments on each mobile

mr_complist      ----  contains list of cell phones companies

mr_mfeatures    ----  contains mobile features and there description

 

What is want is that ::::

 

1)Every mobile phone have there own comments on it which filed I should place in comment table so I can fetch particular comment on particular mobile phone.

 

2)Every mobile phone have there own features how should I should I fetch particular

  Mobile fetures from features table.

  And how on admin side I can add new features to table and delete , modify those feturs

  And every mobile have different features . .

 

Please tell me how should I make my database schema to do these things

 

My incomplete database schema is below

 

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for mr_comments
-- ----------------------------
CREATE TABLE `mr_comments` (
  `id` int(20) NOT NULL auto_increment,
  `mob_id` int(20) default NULL,
  `name` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  `comments` text,
  `comment_ip` varchar(255) default NULL,
  `status` enum('1','0') default '0',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=82 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mr_complist
-- ----------------------------
CREATE TABLE `mr_complist` (
  `comp_id` int(20) NOT NULL auto_increment,
  `comp_name` varchar(255) default NULL,
  PRIMARY KEY  (`comp_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mr_mfeatures
-- ----------------------------
CREATE TABLE `mr_mfeatures` (
  `mf_id` int(20) NOT NULL auto_increment,
  `mob_id` int(20) default NULL,
  `fetures_name` text,
  `fetures_description` text,
  PRIMARY KEY  (`mf_id`)
) ENGINE=MyISAM AUTO_INCREMENT=251 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for mr_mobiles
-- ----------------------------
CREATE TABLE `mr_mobiles` (
  `mob_id` int(20) NOT NULL auto_increment,
  `comp_id` int(20) default NULL,
  `name` varchar(255) default NULL,
  `status` enum('0','1') default '1',
  `image` varchar(255) default NULL,
  `thumbnail` varchar(255) default NULL,
  `timeanddate` datetime default NULL,
  PRIMARY KEY  (`mob_id`)
) ENGINE=MyISAM AUTO_INCREMENT=82 DEFAULT CHARSET=utf8;

 

 

[kkubek]

It's hard to give a complete answer without fully understanding your requirements, but based on a lot of assumptions, your schema is a very good starting point.

 

A couple suggestions:

 

Many of the columns should be changed to NOT NULL. As a matter of fact, it looks to me like every one should be NOT NULL. Even the iffy ones like image, couldn't be populated with "No Image" if one is not available.

 

You'll want to add FKs also. For example, mr_mobiles should have an FK on comp_id that points to mr_complist. And, mr_mfeatures needs FK to mr_mobiles.

 

You need more FKs than that but I don't want to make it too easy  8-)

 

Not sure what other info you're looking for.