[EMAIL PROTECTED],
hi,
i'm created a 'configuration' table:
create cofiguration (
config_key VARCHAR(25) NOT NULL PRIMARY KEY,
config_value VARCHAR(255) NOT NULL
) ENGINE=MyISAM;
I have about 30 records (store_name, store_owner, template_name,
website_width, owner_address, prodcuts_per_page,..).
now i wonder is maybe better solution to have a table with 30 columns and
one record:
create configuration (
store_owner VARCHAR(50) NOT NULL,
template VARCHAR(25) NOT NULL,
store_with INTEGER(4) NOT NULL,
thumb_width int(3) NOT NULL,
big_width int(4) NOT NULL,
products_per_page INT(2) NOT NULL,
stock ENUM('1','0'),
etc.
) ENGINE=MyISAM;
That's a very tough decision to make based solely on database
"elegance", in probability your choice should be coupled to your
software's design.
It would be reasonable to assume that you're using some sort of object
orientated language and if so that you have a “store” object with getter
and setter methods for each of the configuration options which would
match the second table design neatly.
If however your software or system is complicated enough you may have
abstracted the concept of “configuration” out into it's own little
object/interface entirely which you may then subclass/implement in some
fashion depending on your language of choice. This approach would more
closely reflect the first table's design.
Finally and perhaps the decider that could influence your software's
design in the first place is database performance considerations. Taking
a pretty large scenario of say 5,000 stores with 100 configuration
options each you'd either end up with half a million records of about
three columns in table version one or only 5,000 records but with 100
columns in table version two.
Given the options I'd personally go with version one, half a million
records isn't a problem for any sensible hardware and it'll be easier to
administrate and code for in my humble opinion.
If I were to implement this sort of thing I'd do it this way...
CREATE TABLE `Configuration`
(
`Configuration_ID` int(11) NOT NULL auto_increment,
`Store_FK` int(11) NOT NULL,
`Parameter_name` CHAR(255) NOT NULL,
`Parameter_value` CHAR(255) NOT NULL,
`Date_deleted` timestamp NOT NULL default '0000-00-00 00:00:00',
`Date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update
CURRENT_TIMESTAMP,
`Date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`Configuration_ID`),
UNIQUE KEY `Store_parameter_ID` (`Store_FK`,`Parameter_name`),
KEY `Parameter_name` (`Parameter_name`),
KEY `Store_FK` (`Store_FK`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Remember to set Date_created equal to NOW() when you insert (or make
yourself a trigger if your as paranoid as me) and there may also be
value in replacing Parameter_name with a foreign key and normalising it
off into it's own little table depending on how funky you'd like to get.
I should probably point out that I've glossed over/skipped whole degree
course programs worth of database and software design principles and
that my solution is my own opinion and could well be hideously over
engineered or otherwise seriously flawed but you know ;^).
Regards,
Phil
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
