Designing mysql database tips & techniques, and some mysql questions?

Question

Well, I am going to design a database structure, but I don't know where to start. Can you guys help me by answering the following questions? My database is going to store about hundreds of thousands rows/data and serving 1000 ++ users at the same time. It needs to be well designed.

Design mysql database structure questions:

Q1: I know this is a stupid question, but how do you guys design a mysql database structure? Must I study normalization first? I suck at this topic :(

Q2: Is there any tips or techniques when design a database structure?

Q3: What are the important things in designing mysql database structure?

Q4: Okay, another stupid question, what are the differences using mysql database and xml?

Q5: Is there any downside/disadvantages using mysql database?

FYI: I am a new mysql developer.

Answer 1

It's commendable that you're willing to jump in to this project, but in my experience trying to do a real project at the same time as you're learning the basics of any technical subject is likely to result in a project that doesn't work. And it takes ten times as long.

So I would recommend spending some time educating yourself first, by doing some reading and some experimentation. Design a practice database and the code to use it, but not the database you need for your project.

Here's a well-regarded book that might help you:

• Database Design for Mere Mortals(R): A Hands-On Guide to Relational Database Design (2nd Edition) by Michael J. Hernandez

After you are more comfortable with the basics of database design, then you can start your real project.

Answer 2

I haven't done much DB design beyond the project I'm working on now, but one thing I've found that's helped is keeping your design extensible and flexible.

By this I mean don't hard-code a lot of stuff into your records. Start with foreign keys to the information instead. (This gets back to normalizing your DB first.) This way you can link information more ways.

For example, rather than stuff a lot of information about the Manufacturer of a particular Part in the DB row for that Part, make a table just for the Manufacturers, and link to the Manufacturer from the Part entry by means of a foreign key to the Manufacturer table. This way you can add information to the Manufacturer table later without affecting the Part table, and if the Manufacturer of a particular Part changes, you can just change the foreign key rather than all of the fields in every Part that was manufactured by that particular Manufacturer.

That's a 49-second introduction for why you should think about normalizing your DBs. ;)

Anyway, as you learn and have more specific questions, ask them. Best wishes!

Answer 3

Q1: I know this is a stupid question, but how do you guys design a mysql database structure? Must I study normalization first? I suck at this topic :(

Personally when I first started I used phpMyAdmin and a few tutorials online. I am not saying this is the best way to go since I made lots of mistakes at first. So even though you don't need to learn things like normalization it won't hurt.

Q2: Is there any tips or techniques when design a database structure?

Remember that even thought the application you build on top of the database will probably change several times your database will not. By this I mean that it is often very difficult to change the structure of the database once the data has been inserted. Another tip I can give that is often overlooked by people new to databases is the use of indexes. Any column you use that you read form a lot (ie. used in selects, where clauses, joins, etc...) should be indexed.

Q3: What are the important things in designing mysql database structure?

Plan ahead, I would seriously consider creating a UML diagram of the database so you can get a good overview of the database. Don't create a single table until you have the whole idea of your database planned out.

Q4: Okay, another stupid question, what are the differences using mysql database and xml?

I don't think I could answer this question properly so I won't try. Even though XML can be used to store data, I would recommend MySql over it.

Q5: Is there any downside/disadvantages using mysql database?

Compared to what?