General Database Design Concepts - Need advice
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.
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.
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'
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.