database question

[Isaac]

I know with my pickle file databases I was very aware of my primary and foreign keys. This was because I ran in to problems, putting everything on one table would mean reading the whole file from top to bottom each time, which was slow. Also, hard to change. Breaking it up into different text files (tables) made it go faster.

In MySQL how necessary is dividing tables like this? Or is it ok to have everything about a comment box system loaded into one table? Mysql seems to be pretty well made for querying and returning only samples of data, like the last 5 comments associated with a comment box on a particular page.

Or is it always better to break up data into tables with relationships, even in MySQL?

[Jeff250]

You can google database normalization to get a theoretical treatment of this, but the high order bit is to create tables in order to avoid any data redundancy. And the primary appeal for this is correctness more so than performance. If you're storing the same data (that isn't being used as a key) in two different rows, then you're probably doing something wrong. You should split the redundant data off into a single row in a new table and use a foreign key to point to it from the original table.

[R e v]

I think it always better to break out all data relationships across many tables. They are called relational databases for a reason. I try to keep my main tables containing the bulk a given dataset to be predominantly integers referencing tables containing labels, etc...

[Isaac]

Oops forgot about this thread.

Jeff and Rev
here's what I figured I should do. It's pretty basic. Sorry if it's messy looking, I'm still playing with it.

database_Chatbox.png (61.66 KiB) Viewed 1971 times

[R e v]

That's going in the right direction.