1

I'm new to mysql,and I'm designing a database for my social network app, I got some questions about it.

user_table

enter image description here

comment_table

enter image description here

1.Should I use unique key as much as I could in the database when the value of the field is unique?

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future?

3.In the comment_table,I use user_id for foreign key,but I heard using too much foreign key will make the database slow,especially in social network.So,should I use comment_author_id instead,and if I need someone's comment,I have to use

"select * from comment_table,user_table where comment_author_id = user_id"

Here is an graph I found on the internet,it use a lot of foreign key.Is it correct or out of date? Thanks for your time. enter image description here

migrated from stackoverflow.com Jun 1 '15 at 10:45

This question came from our site for professional and enthusiast programmers.

1

Generally, your top priority when designing a relational database is data integrity. Performance is a distant second at best. If you sacrifice data integrity, you may one day defending the charge, "This query returns the wrong answer!" with the rationale, "Yes, but it does so very quickly."

Quite lame as far as excuses go, yes?

However, the overhead of data integrity is small and localized. By localized, I mean that the overhead is generally on the DML (Insert, Update, Delete) operations. The queries will usually perform better.

1.Should I use unique key as much as I could in the database when the value of the field is unique? Absolutely. Always. Never fail. Defining a field as unique creates an index on the field. Odds are good that any unique field will be used heavily in the filtering criteria: where uniquefield = value. Now you have data integrity and fast access.

2.Should I add some backup field in case I need to add other field in the future? If not,how to add other field in the future? No. If you don't know enough to create a field, you don't know the data type, the uniqueness, the nullability, anything needed to properly define a field, including a proper name. It's easy enough to add a field to an existing table once you know all the particulars. There is no need to plan ahead on this.

3.In the comment_table, I use user_id for foreign key, but I heard using too [many] foreign key[s] will make the database slow, ... As I said before, data integrity constraints, such as foreign keys, can make DML a little slower, but foreign keys are completely irrelevant to queries. They are ignored, they play no role at all. So go ahead and define foreign keys, unique constraints and whatever other constraints make sense to keep a tight reign on data integrity.

It makes no sense at all to have excellent response time in a database filled with anomalous data. Fortunately, "response time" in most OLTP databases refers to query time. Proper data integrity checks will, at worst, have no effect on query response time. At best, they will actually make it better.

This is where good analysis comes in. In SQL tuning, you typically speed up query time by sacrificing DML time -- and vice versa. Typically, response time concerns for OLTP databases lean towards queries. However, there are instances where the DML is the primary concern. So which hits your tables most often: DML or queries? Generally, it's not even close -- like 100 to 1 in one direction or the other. If you can speed up the 100 side by moving a little overhead to the 1 side, so much the better.

But never sacrifice data integrity. It's never worth it.

  • Thanks a lot !! TommCatt,I'm very love your answer,and it helps me a lot. – Windsooon Jun 7 '15 at 14:18
-1

First question:

You cannot be sure that user_name will be unique in 100%, so you must assume duplicates in that column. In that case unique cannot be applied.

Of course, you force unique fields whenever you can, because that makes space for later indexing, which will significantly increase performance.

For user_email field, it have sense to be unique.

Second question:

It is better practice to not add extra field for some future attributes and name it just as backup_field. Exclude those fields from your design, and if needed later, add it and change a code to cover those fields.

Third question:

Your current design for comments is right, only if you have solution that one comments can add two people, then create additional table with adequate foreign keys to author and comment

  • 1
    Why would you want to allow duplicate user names? I think that most social networking sites don't allow that. – nvogel Jun 2 '15 at 5:16

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.