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.

3

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.

  • 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 '15 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 '15 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 '15 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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