Mysql Table Design (one Big Table Or Many Tables)?
I have a table that stores people information. However each person could have a different status such as employee, client, supplier.
All 3 share like 10 fields in common (name, lastname, telephone, etc), however depending on their profile (employee, client, supplier) each one needs specific information.
The thing is that the specific information for each one of them requires like 15 to 20 fields each, and the table has grown to be like 65 fields, so I guess my question is:
Should I store everything in one table called "people" with like 60 or more fields.
OR
Should I create a structure such as:
people
people_employees
people_clients
people_suppliers
The 1st will contain common information and the others specific information depending on the person's profile.
What is better to have one single table or to have different tables in order to avoid null values.
What other efficiency problems could arise?
I hope is not that confusing,
Thanks a lot
Possibly related posts: (automatically generated)
- Related posts on Design
- California Web Design – Choosing The Best To Get The Job Done ...
- How Web Design Can Dictate The Popularity Of The Site at Next ...
- The Value of Design – The Power to Influence, Educate & Inspire ...
- Related posts on Mysql
- PHP MySQL Freelancer | Web Developers Di « Freelance web design Blogs
- Whoila Blog » Blog Archive » SqlYog keeps crashing when I do mysql ...
- Pro MySQL :Computing Guy
 |
US $19.99
Tagged with: Design • Many • Mysql • Table • Tables
Filed under: Telephone Table FAQ
Like this post? Subscribe to my RSS feed and get loads more!
In my opininon, one table with all info can result in a catastrophy if something happens. Better with one table for each “category”.
And always take incremental backups often.
Try to be more accurate & more organized
use the second one (create a structure)
Good Luckhttp://www.q8ieng.com
By having one big table your defeating the whole concept of relational databases.
You have the SDD or Standardized Data Demographic the main record which is the parent to the child records.
The one to many so yes break it down that’s what a database is.
However it looks a tadge dodge your structure
tbl_people = Main SSD
tbl_people to many tbl_clients
tbl_suppliers will be another SSD
I depends what you want to see as the end version of the database but I see where you’re going on this one
Doug
Personally, I would use one table per type of person, however there are legitimate scenarios where it’s preferable to keep everything on one table.
One table keeps the sql access simple. If the added complexity doesn’t concern you, or you are using an ORM tool then go for the structured approach.
One table can also be faster, because all data for a person is physically located in one place on disk.
One table per field is overkill. If I want a users first name I am highly likely to also want their last name, so it’s more efficient to put those two fields in the same table.
First let’s lay it all out. You have a database, and the database is made up of tables and the tables have columns. If you want to minimize the queries and load on your database you can make tables for each attribute and use the people as the key field for the queries.
The primary efficiency you should be concerned about is lengthy queries and expansion. If you want minor load on your server and the ability to expand, make each field a separate table and on your front end make people the primary variable all queries start from. Then if you ever need to drop tables or add tables it’s not dependent on the people table because all tables are separate.
Basically, use the second structure without the prefix and make all table contingent on the same key factor, like this:
Database: A
Table 1: First Name
Table 2: Last Name
Table 3: Title (employee, client, supplier)
So if you need the name of every supplier, the key identifier would be the Title/Status table.
i.e. if $title=(‘employee’);
then show whatever results;
else die;
Lemme know if this helped at all. I’m probably better at showing you via a diagram.
it gets to the point lol
see what you think on this one, might match what you have in mind
Have a look at this guide.
http://www.freewebmasterhelp.com/tutorials/phpmysql
Assuming your host is the same, your site/domain is the same, and the structure didn't change, everything should be fine.
The WordPress database is very stable and easy to move, even from site to site. Like the other answer says, as long as your site is working, you're probably going to be just fine.
Definitely make sure the wood is completely dry. Once it is, make sure the wood has been smoothed down (by sanding), then apply a primer, then paint. Good Luck, and Have Fun Painting them!
>I’ll let him away with it. He has to do at least one daft thing per day or his head might explode….< there are SO many jokes i want to make right now, but i shall resist. #sorryalison
Very informative…thanks
You really have missed it haven’t, you came in on a conversation you know nothing about, I did not insult the inventor, I defended him, someone made a statement that the eei would not like it because it has no earth wire, I defended it by saying it didn’t need it for the application it was designed for, you obviously missed that, and then threw in your uninformed comment, so read it all again then apologise for being stupid!
Mid-table matches are actually entertaining to watch..Football Syndrome mode: *ON*
Wow, lousy weather in NYC area tonight – hope no one has anyone flying in or out! Many power outages…
Now days its same job (web and graphic designing), most companies now wants their employees to be specialized in both things. Go to any good web design company website like exposurewebservices.co.uk, you will see they call themselves web designers, but they actually make website and graphics.
It would be awesome if there were a transcript for this somewhere, even if it were to be some whacked out voice recognition speech-to-text. I am interested but just don’t have time to sit and watch.
music innapropriate….
Turn The Tables On Google And Uncover A Whole New World Of Profit Potential 99% Of Marketers Have No Clue About….