3

I'm creating a blogging site that anyone can sign up and be an author for. I want each author to have their own public profile. My main question is; would it be better practice to have a separate profile table that contains fields that would be specific to only the profile page, or should I just add those profile fields to a general users table as this example shows:

CREATE TABLE IF NOT EXISTS `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `username` varchar(255) NOT NULL,
 `first_name` varchar(255) NOT NULL,
 `last_name` varchar(255) NOT NULL,
 `email` varchar(255) NOT NULL,
 `password` varchar(32) NOT NULL,
 `sign_up_date` date NOT NULL,
 `activated` enum('0','1') NOT NULL,
 `admin` int(1) 0 ,
 `bio` text NOT NULL,
 `profile_pic` text NOT NULL,
 PRIMARY KEY (`id`)
)

bio and profile_pic are both profile fields in this case. This is also just an example, I plan to have more fields for general user stuff and not to many more for the public profile, but that could change as I move further into development.

Sorry if this an opinion and not a best practice question. I'm new to Database architecture.

Improve this question

1 Answer 1

Reset to default
4

Keep them together. You're describing vertical partitioning, which is generally only used when a table is getting really wide - dozens or hundreds of fields. Until and unless there's a compelling reason to separate them (e.g., a single blogger can have multiple profiles, for different blogs, audiences, or languages), there's no need to complicate the schema.

Improve this answer
3
  • Another useful case for Vertical Partitioning is if some of the fields are really bulky (TEXT/BLOB) and are not needed all the time.
    – Rick James
    May 12, 2015 at 23:59
  • Interesting, @RickJames. My understanding in MS SQL is that such columns are always stored out-of-row, so vertical partitioning isn't useful, but it seems that MySQL is a little more complicated.
    – Jon of All Trades
    May 13, 2015 at 0:40
  • There are several flavors (at least 4) row-format in InnoDB, so I guess "it depends". And (if I recall correctly) you need 5.6.3 (or later), innodb_file_format=Barracuda, innodb_file_per_table=ON, and ROW_FORMAT=DYNAMIC;
    – Rick James
    May 13, 2015 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.