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: 7127
Joined: Mon Aug 01, 2005 8:47 am
Location: Ơ̸̦͇̲̬̭̱̰͎̞͈̣͎͚̳ͬ͋̃̀̇͊͂͋͐ͦ̽ͣ̂ͥ͊̅̀̚͠ B̶͖̯͉̜̰̲̓̔͋̈́ͅ È̯ Y̪̤̼͉̠̙͝

database question

Post by Isaac » Sat Sep 17, 2011 11:55 am

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?
s☼-£♦и̫͍ͥ̍ͪ͌̓͗͡о̡̹̱͊̅ͮ̓̕͢б̧̝̻̪̤̳̜͐̓̉ͤ͢͜ ͙̬͙̆̑ͮ̐ͭ̾̂́͘i̎̌̾̓̽̀̈̓̀҉͉̙̦͎̘̝͕f̻͕͔̘ͣͣ̓͊̿͢͜ ͍͔͈͕̮̫ͣ̆ͮ̊͋/♂6Æ!♪╩"▲L└уͭ̂͐̇҉̴̣̼̞̠̯͓̺̞ф̜̊͌̈́̋̏̐́ц̨͔̮̿̇ ̨̛͖̙͖̖̮̗̱ͩ̆͞ͅа̥͇̞̖͚̟̅͐ͤ͞͠͠э̜̘̩̳̬͔̾ͯ̀ͫ̒̐̿ͅͅг̭̖̀ͦ̒̑ͥ̌ͮͫ͞ё͔̟̃ͬ̾̓͟ё̦̞̙̫͔̩͑̀͂ͯ̄̔̃̑̀͠ͅͅ
User avatar
Jeff250
DBB Master
DBB Master
Posts: 6387
Joined: Sun Sep 05, 1999 2:01 am
Location: ☃☃☃

Re: database question

Post by Jeff250 » Sat Sep 17, 2011 3:21 pm

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 » Sun Sep 25, 2011 11:13 am

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: 7127
Joined: Mon Aug 01, 2005 8:47 am
Location: Ơ̸̦͇̲̬̭̱̰͎̞͈̣͎͚̳ͬ͋̃̀̇͊͂͋͐ͦ̽ͣ̂ͥ͊̅̀̚͠ B̶͖̯͉̜̰̲̓̔͋̈́ͅ È̯ Y̪̤̼͉̠̙͝

Re: database question

Post by Isaac » Sun Sep 25, 2011 2:49 pm

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 1063 times
s☼-£♦и̫͍ͥ̍ͪ͌̓͗͡о̡̹̱͊̅ͮ̓̕͢б̧̝̻̪̤̳̜͐̓̉ͤ͢͜ ͙̬͙̆̑ͮ̐ͭ̾̂́͘i̎̌̾̓̽̀̈̓̀҉͉̙̦͎̘̝͕f̻͕͔̘ͣͣ̓͊̿͢͜ ͍͔͈͕̮̫ͣ̆ͮ̊͋/♂6Æ!♪╩"▲L└уͭ̂͐̇҉̴̣̼̞̠̯͓̺̞ф̜̊͌̈́̋̏̐́ц̨͔̮̿̇ ̨̛͖̙͖̖̮̗̱ͩ̆͞ͅа̥͇̞̖͚̟̅͐ͤ͞͠͠э̜̘̩̳̬͔̾ͯ̀ͫ̒̐̿ͅͅг̭̖̀ͦ̒̑ͥ̌ͮͫ͞ё͔̟̃ͬ̾̓͟ё̦̞̙̫͔̩͑̀͂ͯ̄̔̃̑̀͠ͅͅ
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 » Sun Sep 25, 2011 3:14 pm

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