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
dBforums
>
Design for web hosting plans
08-20-04, 00:40
Linear Mode
