Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    2

    Design for web hosting plans

    Hi,

    I'm at present trying to design a simple db, with a few tables, to be used for web hosting plans. Some of the basic information that needs to be stored is:

    * Plan ID
    * Plan Name
    * Plan Cost (hhm, ... multiple currencies ??)
    * Plan Features

    Based on a plan and a feature, some data examples:

    Plan A - feature email accounts - 5
    Plan B - feature email accounts - 10

    Feature values will be different for different plans, in fact, some plans may not have _that_ feature, but then again, I would rather have "not available" for the feature, rather than no row for that plan/feature combo.

    I think the data type of feature_value needs to be VARCHAR, so I can have

    * Number
    * Text (i.e. "unlimited" or "no setup fees" or "yes/no" )
    * Possibly add html tags , for colouring, highlighting,etc ??

    There are of course, many features to a single plan. Here is my test attempt at designing this ........

    Code:
     # phpMyAdmin SQL Dump
     # version 2.5.6
     # http://www.phpmyadmin.net
     #
     # Host: localhost
     # Generation Time: Aug 14, 2004 at 02:24 AM
     # Server version: 4.0.20
     # PHP Version: 4.3.8
     # 
     # --------------------------------------------------------
     #
     # Table structure for table `wh_features`
     #
     # Creation: Aug 14, 2004 at 02:09 AM
     # Last update: Aug 14, 2004 at 02:11 AM
     #
     
     DROP TABLE IF EXISTS `wh_features`;
     CREATE TABLE `wh_features` (
       `feature_id` int(11) NOT NULL default '0',
       `plan_id` int(11) NOT NULL default '0',
       `feature_value` int(11) NOT NULL default '0',
       PRIMARY KEY  (`feature_id`,'plan_id')
     ) TYPE=MyISAM COMMENT='Web Hosting Features';
     
     
     # --------------------------------------------------------
     #
     # Table structure for table `wh_features_description`
     #
     # Creation: Aug 14, 2004 at 02:22 AM
     # Last update: Aug 14, 2004 at 02:22 AM
     #
     
     DROP TABLE IF EXISTS `wh_features_description`;
     CREATE TABLE `wh_features_description` (
       `feature_id` int(11) NOT NULL default '0',
       `feature_name` varchar(30) NOT NULL default '',
       PRIMARY KEY  (`feature_id`)
     ) TYPE=MyISAM COMMENT='Web Hosting Features Description';
     
     # --------------------------------------------------------
     
     #
     # Table structure for table `wh_plans`
     #
     # Creation: Aug 14, 2004 at 02:03 AM
     # Last update: Aug 14, 2004 at 02:06 AM
     #
     
     DROP TABLE IF EXISTS `wh_plans`;
     CREATE TABLE `wh_plans` (
       `plan_id` int(11) NOT NULL default '0',
       `plan_name` varchar(30) NOT NULL default '',
       `plan_cost` decimal(10,0) NOT NULL default '0',
       PRIMARY KEY  (`plan_id`)
     ) TYPE=MyISAM COMMENT='Web Hosting Plans';
    I have tried to normalise this as much as possible. Most plans will have all the possibilties of features, but some may not.

    1. Is it more effecient to have the order of the columns in the table "wh_features" like this:

    plan_id
    feature_id

    I guess my thinking was, for data like this:

    11
    21
    31
    41
    51
    61
    12
    22
    32
    etc,etc

    it is sometimes stated to try and organise the sequence of the columns, such that the natural order of the data will be like this:

    11
    12
    13
    14
    15
    16
    21
    22
    etc,etc

    and then, any indexing, searching, or a full scan of the table, will take less time. I think the theory behind doing that was, key optimisation was always going to take the shortest path, but if the natural order of the data is better suited to how _most_ of the queries are done, it will take less time. Maybe that is old thinking, it was based a lot on (disk) head movement, and now I'm showing my age.

    2. Are there any other design improvements, that can be made ?

    Thanks,

    Peter

  2. #2
    Join Date
    Aug 2004
    Posts
    2
    Hi,

    Had a minor change on this design. Is this better suited for the needs of this small project ??

    Code:
    #
    # Table structure for table `xoops_wh_features`
    #
    
    CREATE TABLE `xoops_wh_features` (
      `feature_id` smallint(5) unsigned NOT NULL default '0',
      `feature_name` varchar(30) NOT NULL default '',
      PRIMARY KEY  (`feature_id`)
    ) TYPE=MyISAM COMMENT='Web Hosting Features';
    
    # --------------------------------------------------------
    
    #
    # Table structure for table `xoops_wh_plan_features`
    #
    
    CREATE TABLE `xoops_wh_plan_features` (
      `plan_id` smallint(5) unsigned NOT NULL default '0',
      `feature_id` smallint(5) unsigned NOT NULL default '0',
      `feature_value` varchar(20) NOT NULL default '',
      PRIMARY KEY  (`plan_id`,`feature_id`)
    ) TYPE=MyISAM COMMENT='Web Hosting - Plan features';
    
    # --------------------------------------------------------
    #
    # Table structure for table `xoops_wh_plans`
    #
    
    CREATE TABLE `xoops_wh_plans` (
      `plan_id` smallint(5) unsigned NOT NULL default '0',
      `plan_name` varchar(30) NOT NULL default '',
      `plan_cost` decimal(10,0) NOT NULL default '0',
      PRIMARY KEY  (`plan_id`)
    ) TYPE=MyISAM COMMENT='Web Hosting Plans';
    Thanks,

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •