database question

For all coding issues - MODers and programmers, HTML and more.

Moderators: Jeff250, fliptw

Post Reply
User avatar
Isaac
DBB Artist
DBB Artist
Posts: 7736
Joined: Mon Aug 01, 2005 8:47 am
Location: 🍕

database question

Post by 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?
❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉
-⎽__⎽-⎻⎺⎺⎻-⎽__⎽--⎻⎺⎺⎻-★ ·:*¨༺꧁༺ :E ༻꧂༻¨*:·.★-⎽__⎽-⎻⎺⎺⎻-⎽__⎽--⎻⎺⎺⎻-
❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉
User avatar
Jeff250
DBB Master
DBB Master
Posts: 6530
Joined: Sun Sep 05, 1999 2:01 am
Location: ❄️❄️❄️

Re: database question

Post by 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
DBB Admiral
DBB Admiral
Posts: 1121
Joined: Wed Mar 24, 1999 3:01 am

Re: database question

Post by 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...
User avatar
Isaac
DBB Artist
DBB Artist
Posts: 7736
Joined: Mon Aug 01, 2005 8:47 am
Location: 🍕

Re: database question

Post by 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
database_Chatbox.png (61.66 KiB) Viewed 1969 times
❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉
-⎽__⎽-⎻⎺⎺⎻-⎽__⎽--⎻⎺⎺⎻-★ ·:*¨༺꧁༺ :E ༻꧂༻¨*:·.★-⎽__⎽-⎻⎺⎺⎻-⎽__⎽--⎻⎺⎺⎻-
❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉❉⊱•═•⊰❉⊱•═•⊰❉⊱•═•⊰❉
R e v
DBB Admiral
DBB Admiral
Posts: 1121
Joined: Wed Mar 24, 1999 3:01 am

Re: database question

Post by R e v »

That's going in the right direction. :)
Post Reply