General Database Design Concepts - Need advice

[wh33t]

Hey Db Guru's, all of this general banter is directed towards you guys.

The website I'm working on is becoming a bit of nightmare to update and alter. I think this is mostly due to the fact that there is a lot of very similar features on the site that each get their own custom code and table in the database to operate. I am under the impression that if I can somehow combine a lot of this repeat functionality and feature into a few very effective and steam lined scripts and tables the website will be easier to manager. So for example I have quite a few tables such as "users" (that holds general user info like f_name), and then there will be an accompanying table called "user_addresses" (where the user would store multiple shipping/billing addresses). Then there will be a table like "order_forms" (that holds the specifics of the order_form such as date_created, order_form_name, order_form_description) and then there will be an accompanying table called "order_form_items" (which just holds order_form_id,item_id) which holds records of which items should appear in this order form.

Pretty much the entire database is filled with a main records table, then a supporting data table (such as users and user_addresses). I am thinking I could do away with all of the supporting tables and just replace it with a one-size-fits-all supporting table. This table would have a large number of columns of the column types I need. Let's say 20 x varchar 255, 20 x int 11, 20 x tinyint 1, 20 x mediumtext, 20 x mediumblob etc. Then for each section of the website, let's say for example the "users" section I would have it load a feature.config.php script. This script would hold an array that tells some Mysql Code what data to grab from the one-size-fits-all table.

Something like:

user_addresses.config.php

varchar_1 = 'user_address'
varchar_2 = 'user_postal_code'
varchar_3 = 'user_phone'
varchar_4 = 'user_city'
mediumtext_1 = 'user_address_description'
mediumtext_2 = 'user_shipping_notes'
tinyint_1 = 'default_address' 

Once this config file is read the general script that reads and writes data into this one-size-fits-all data table would know how to read/write/remove information. I know I'm technically capable of building this, but I'm not really sure this is what I want to be doing, I'm especially not sure if this will impact the Mysql server negatively (the database is relatively small though, less than 20,000 records across 31 tables). This website is hands down the biggest project I've ever worked on and it's only me working on it and I think it's time to admit to myself that I'm overwhelmed with it's complexity. I need to simplify it and this is one of the ideas I've come up with.

I always value the DB experts here on Site Point. You guys have helped me learn so much in such a short time and I have grown because of this help. So as always, I truly appreciate all of your constructive feed back and suggestions.

[r937]

I am thinking I could do away with all of the supporting tables and just replace it with a one-size-fits-all supporting table.

google "one true lookup table" (OTLT)

in general, it's a bad idea, and will give you more headaches than it will solve

with multiple separate similar tables, you will have multiple code blocks which are also similar -- but that's why copy/paste was invented, to make it easier to generate the code for each set of tables

the minute you try to "generalize" you introduce a level of complexity that will come back and bite you, and it will be a lot harder to fix problems if all of your code is potentially disrupted rather than an isolated code block

[oddz]

ditto r937

The way you have been proceeding with separate tables is the correct way, though more labor intensive. Using separate tables, with specific purposes will be more efficient and easier to maintain.

What you have come up with might sound good/clever but it would be a nightmare to manage and pretty much goes against everything a relational database stands for.

if the next person that came along knew what they were doing they would probably want to burn you at the stake for doing such a thing. At the very least make it clear to the employeer that the person was a fraud.

Saying that though I work with Drupal where "developers" love to serialize data entities and toss them into a single column. many of the people who have made those decisions are top players in the Drupal community and still going strong. So who knows… though at times I really want to murder any/all responsible for those types of uneducated decisions.

Anyway, yeah – it is not in your nor the clients best interest to deviate from using separate tables.

[wh33t]

Thanks for your advice guys. I spent the better part of my evening reading the pro's and con's (mostly the latter) of the OTLT method. While I never planned to use just one table to store everything, I was thinking about using 3 or 4. I read a lot of very interesting material on the matter and I had to laugh at some of the things database designers mock programmers for and I'm totally guilty of many. I've concluded it makes more sense to focus on code/sql generation (which I actually really love doing anyways) and I think that's where I'll spend a fair bit of time planning it all out.

Looks like I dodged a bullet!

[Lemon Juice]

This is interesting because I've never heard of the term OTLT and I also think it's a bad idea but I can see myself having implemented somthing like this in one of my databases. The reason was I couldn't think of any better way. The scenario is: I want to track all changes that admin users make to orders. The obvious design choice came to my mind - have a log table where each row has information about a single change a user made at a certain point in time. But the problem is there are many different types of changes a user can make:

- change data of a field - there any many fields like name, surname, street, status, etc. where each can have a different lenght and can also be of a different type
- change a boolean property (checkboxes) of a field
- add a product
- remove a product
- change price of a product
- change quantity of a product
- etc...

How do I store in a single row data of such big variety? What I ultimatelty wanted to do is simply display the list of changes with their descriptions under a given order sorted by date and time - in a readable table. I thought that creating a separate table for each type of change will be a nightmare because I will end up with more than 10 separate tables just for the sake of this changelog. So I decided to use just one table with all fields I might need, general TEXT column to contain any type of data and of course most of the columns end up being NULL. Then my application interprets the data and changes into human readable descriptions.

This is the table (simplified for the sake of this discussion):

CREATE TABLE `order_change` (
	`change_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_id` MEDIUMINT(8) UNSIGNED NOT NULL,
        `change_time` DATETIME NOT NULL,
	`change_type` CHAR(2) NOT NULL,
	`field` VARCHAR(25) NULL DEFAULT NULL,
	`old_value` TEXT NULL,
	`new_value` TEXT NULL,
	`product_position` SMALLINT(5) UNSIGNED NULL DEFAULT NULL COMMENT 'position of product in order',
	`prod_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
	`product_name` VARCHAR(255) NULL DEFAULT NULL,
	`vat` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`qty` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
	`price` DECIMAL(9,2) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`change_id`),
	INDEX `FK2_order_id` (`order_id`),
	CONSTRAINT `FK2_order_id` FOREIGN KEY (`order_id`) REFERENCES `shop_order` (`order_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
ENGINE=InnoDB;

I know it's a bit ugly, one table holds all types of data. I still can't think of a better way, does anyone have any ideas? The question is theoretical because the system I made works very well but I'm left with a feeling this design is a bit ugly.

[wh33t]

This is interesting because I've never heard of the term OTLT and I also think it's a bad idea but I can see myself having implemented somthing like this in one of my databases. The reason was I couldn't think of any better way. The scenario is: I want to track all changes that admin users make to orders. The obvious design choice came to my mind - have a log table where each row has information about a single change a user made at a certain point in time. But the problem is there are many different types of changes a user can make:

- change data of a field - there any many fields like name, surname, street, status, etc. where each can have a different lenght and can also be of a different type
- change a boolean property (checkboxes) of a field
- add a product
- remove a product
- change price of a product
- change quantity of a product
- etc...

How do I store in a single row data of such big variety? What I ultimatelty wanted to do is simply display the list of changes with their descriptions under a given order sorted by date and time - in a readable table. I thought that creating a separate table for each type of change will be a nightmare because I will end up with more than 10 separate tables just for the sake of this changelog. So I decided to use just one table with all fields I might need, general TEXT column to contain any type of data and of course most of the columns end up being NULL. Then my application interprets the data and changes into human readable descriptions.

This is the table (simplified for the sake of this discussion):

CREATE TABLE `order_change` (
	`change_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`order_id` MEDIUMINT(8) UNSIGNED NOT NULL,
        `change_time` DATETIME NOT NULL,
	`change_type` CHAR(2) NOT NULL,
	`field` VARCHAR(25) NULL DEFAULT NULL,
	`old_value` TEXT NULL,
	`new_value` TEXT NULL,
	`product_position` SMALLINT(5) UNSIGNED NULL DEFAULT NULL COMMENT 'position of product in order',
	`prod_id` MEDIUMINT(8) UNSIGNED NULL DEFAULT NULL,
	`product_name` VARCHAR(255) NULL DEFAULT NULL,
	`vat` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
	`qty` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
	`price` DECIMAL(9,2) UNSIGNED NULL DEFAULT NULL,
	PRIMARY KEY (`change_id`),
	INDEX `FK2_order_id` (`order_id`),
	CONSTRAINT `FK2_order_id` FOREIGN KEY (`order_id`) REFERENCES `shop_order` (`order_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
ENGINE=InnoDB;

I know it's a bit ugly, one table holds all types of data. I still can't think of a better way, does anyone have any ideas? The question is theoretical because the system I made works very well but I'm left with a feeling this design is a bit ugly.

Maybe an SQL database is overkill for a simple logging feature? Maybe you would better off just appending to an XML file?

Something like

<event>
  <date>Sept 20th/2012;15:00</date>
  <type>Form element change</type>
  <user>admin</user>
  <ip>xxx.xxx.xxx.xxx</ip>
</event>