I'm new to mysql,and I'm designing a database for my social network app, I got some questions about it.
user_table
comment_table
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.
Best Answer
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.