1

I want to know what is better.

  1. save all data (for example skype,icq,facebook,stackoverflow,etc,etc,etc) profiles in one column
  2. save data in separated columns (separated column for skype, icq, facebook, stackoverflow, etc,etc)

What is better and easy for mysql server?

Improve this question
1
  • 1
    What does your data look like? What makes better sense in terms of relationships? It depends on the structure of your data. Commented Jun 4, 2011 at 20:38

3 Answers 3

Reset to default
4

Saving them all in one column would violate first normal form and make querying and aggregating the data very difficult.

Use one column to store each piece of information.

If you are storing profile data for each service, you might even be better of with a separate table per service, with a foreign key to the user id.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Good general advice. SQL is pretty efficient, if your queries are efficient.
OK. forgot about rebuilding problems, I'm asking just for selecting case. If sql has less columns to "search", it has less reading from HDD.
@genesis - Not necessarily. It depends on how many columns and the column sizes. You are in the design stage of you database - you are doing premature optimization (if it is even optimizing).
@Oded: As I said, I do not care about problems when proccessing these data from my side, but I'm interested how would server take them (faster/slower), it's just theoretically, I'm just curious and I did not want to use it in future. Just asking
@genesis - It is not possible to answer how performance will be with either of these approaches, not theoretically. You would need to setup benchmarks using both approaches and see which ones is more performant.
|
2

Beyond discussions with respect to normalization which might seem abstract, asking the right questions can help make it clear why one approach might be superior to another.

  1. Would you want to query for specific accounts? E.g., find all Skype accounts or find a specific Skype account. Of your two approaches, which makes that easier?

  2. Which approach do you suppose would be easier to correct if there was a problem with the storage of one of the account types?

  3. Which approach do you suppose would be easier to expand the types of accounts? E.g., today you build for Skype, ICQ, Facebook and StackOverflow. Tomorrow, you realize you want to store Twitter accounts. Which approach do you suppose would make that easier?

  4. Which approach do you think would be easier for another developer to grasp, maintain and expand? E.g., your system becomes so popular that you hire developers to take over your work. The approach that is more inline with industry best practices will be easier for other developers to understand.

When you analyze the problem like this, it should be clear that storing the information into separate columns or perhaps a table with accounts where each user can have multiple rows that represents multiple accounts is the superior approach.

Improve this answer

5 Comments

1. I won't search in that column. 2. you mean in case of server/table crash? 3. yep, that's true. Everything you told here is true, but now I REALLY WOULD LIKE TO GET RIGHT ANSWER, it means, WHAT IS BETTER for SERVER, not for ME. So ? However Thanks for answer
@genesis - RE: #2. Not a crash. Suppose there is an error in the way you are storing the information that you discover after the system goes into production or there is a correction needed to the way you are storing the information.
you mean that I would need to reset all ICQs but keep Skype data, which would be impossible. Am I right?
@genesis - The right answer is to leverage the process of database normalization to determine how best to store the data. That means storing discrete data elements in their own column. A user's Stackoverflow account information is separate and distinct from their Twitter account information. Thus, they should be stored as separate values. The scope of "what is best for the server" is too narrow. What is best for performance, maintenance, and extensibility?
@genesis - RE: #2 It would be far more cumbersome to correct than if the data were stored as separate values in separate columns. Btw, if you are thinking about a column for SO, another ICQ, another for Twitter etc, that too would be less than ideal. A table of accounts with a foreign key to the user would be better.
1

You will gain much more flexibility if you use multiple columns.

If you would like to query the database based on one of these fields, it would be very difficult if it is all packed together into one column. It also makes maintaining the database more difficult for yourself and whoever comes after you.

Improve this answer

Comments

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.