Couple questions about efficient database design.

Hello all, I am currently putting together a database design for a web application I want to build. And I would like your opinions on a few things.

The prototype for this app will have to be written with MySql (and the code in PHP), so if you start talking about specific database things, please let me know if MySql supports that technique.

The application will have users.

1) If I wanted to store info about the users, would it be efficient to give each user a unqiue ID (duh) and then each time a user adds a piece of info the database will store thier UID and the info into a new row.

To show an example, the table might look like this:

PK|UID|Info
-----------
1 |001|I am cool
2 |002|I think I rock
3 |001|I love me some coolness
4 |003|Trueness is coolness
5 |002|Rock'n like a hurricane
6 |001|More Info

Of course the UID will relate to that user's profile, which is on another table.

If this table builds to millions of rows is this an efficient way to store this data? By efficient I mean each time I want the app to gather a user's info is the "proper" way to have it stored?


2) Another question, let's say that I want the app to keep a user's recently
queried data "on hand", i.e. useable from page to page. So It can be reused while a user is logged in. Is session variables the only way to go with this???? I would imagine if there were quite a few users this would put a termendous strain on the server.

Well, I will probably have many more questions in the future, thank you for taking the time to read this post, and Thanks more if you can offer any help.

~out.

Hi there :)

First off, yes that does sound like a good idea to store UID with the data the user posted; it's how most systems work. Generally you want to store data about each seperate "entity" in one table - so you want a users table, an articles table (if your users were posting articles of course :P), etc. etc. And of course they have to be somehow related - you need to know which user created which article etc.

The best way to do this is how you've described - giving each user a unique ID number and storing that with each article. It's definitely the best way to do this, even when you get into large numbers of records - because any other method has larger drawbacks. To give one example, you could store user information (name, email address etc.) in the table with every piece of data they post (e.g. in the articles table from above). However, this means you're duplicating a load of data - you're storing the user's name and email address and whatever else multiple times. That's inefficient, and what happens when he wants to change his email address? You have to update every single occurance in every table - which is bad. The way you've described is infinitely preferable.

[Edit: meant to add that this isn't database specific, it's just a general point about database design - so it applies to any DB you might use]

For your second question, I assume you mean that you want to cache data so that you don't have to hit the database every time? Session variables are the easiest option for this if the amuont of data is small. You could also use a cookie if the data isn't sensitive (i.e. it isn't credit card details etc. ;)), or you could write the data to a text file on the server and use that. However, generally speaking if it's large chunks of data you're talking about you might be better off leaving it in the database anyway - as you say Session variables storing large amounts of data will slow your app down, and text files aren't always any faster than database hits.

Hope that's of some use to you :)

Thank you very much for your reply. I posted this question on a few other forums, and I've gotten only a limited response.

thanks again!

do you think making a temporary table for this info would be a good idea? or is that an even bigger hit to system resources?