Acmlm's Board - I2 Archive - Programming - MySQL!
User | Post |
neotransotaku
Posts: 3713/4016 |
Mines the same thing, just more stuff to speed up queries and to ensure uniqueness
So, the syntax would be along the lines of...
Create table games (gameTitle varchar(30) not null, console varchar(10) not null, codeTitle varchar(50) not null, code varchar(30) not null, primary key (gameTitle, codeTitle));
That just creates the table (with columns of gameTitle, console, codeTitle, code) and ensures that you can not enter the same code title twice for the same game.
As for creating an index, you do the following:
create index gameIndex on games(gameTitle) create index consoleIndex on games(console)
As for your two SQL statements,
- SELECT FROM games WHERE console = 'N64' -- this works as long as all your N64 entries are group together. However, if you seperate each game into it's own table, then you won't get all your games unless you iterate through each table
- SELECT FROM N64MarioKart" -- first you need to say 'SELECT * FROM N64MarioKart'; and second, your above query would miss this table unless the data in this table is also in your Games table -- but then you would have redundant (and possibly inconsistent) data storage because not only would you have to update the games table, you would also have to update your N64MarioKart table
HyperLamer
Posts: 6193/8210 |
I'm not sure what Neo means, having never used SQL before. (Haven't managed to get anything working yet, some sort of password problem...) I like Argos' idea, but I'm not entirely sure how it works. What I'm thinking now is to have one table listing all the games by game ID, name and console (like 'Mario Kart 64', 'MarioKart', 'N64'). So say you wanted a list of N64 games, something like "SELECT FROM games WHERE console = 'N64'" should do it, and if you look up Mario Kart, you would do "SELECT FROM N64MarioKart" or something along those lines...? (The console list can just be a hard-coded page, it's not going to be changing much. ) |
neotransotaku
Posts: 3712/4016 |
Many rows will slow down the server if you have a poor underlying format for a table and/or you are searching on a non-index. But if you use a data structure called a B+ Tree, you can get the data of single row in multi-million row table with a constant amount of I/O seeks (typically three or four) as long as you search on the index. That is why I suggested HH to use an index, it will speed up his queries significantly if he does searching/add/deleting via a game title |
DarkSlaya
Posts: 3920/4249 |
Originally posted by neotransotaku
The fewer tables you can use, the better things will be.
I agree with that.
But if there's alot of rows, wouldn't it slow down the server (I think I heard that somewhere ) |
neotransotaku
Posts: 3711/4016 |
one table is best with an index on the game, and perhaps an index on console and use a primary key of (game, code) title to take care of uniqueness
The fewer tables you can use, the better things will be. |
Narf
Posts: 76/100 |
I think working with ID numbers would be better and easier for you right now. However, if you get more experienced with queries, in particular using 'LIKE' in SQL queries, give it a go with names.
There are tons of ways to do what you want to do, try out a few of them, should do your MySQL learning some good. |
DarkSlaya
Posts: 3916/4249 |
Personnaly, I believe that using IDs for both would be easier and as efficiant as you would want.
Since you only have to do queries like:
$code = mysql_fetch_array(mysql_query("SELECT stuff FROM games WHERE gameid='".$_GET['gid']."'");
or
$gamebyconsole = mysql_fetch_array(mysql_query("SELECT stuff FROM games WHERE console='".$_GET['cid']."'");
Dunno, seems the best way to do it. |
HyperLamer
Posts: 6185/8210 |
Well I've finally decided to start screwing around with MySQL a bit. What I'm aiming to create is a database of codes for games. Each code has attributes like the name, submission date and of course code; I imagine one code = one row. Now I'm wondering how to best handle multiple games. Would it be better to simply add a "game" field to each row, and select only rows where this field matches the game's name/ID, or create one table per game? (I'm going with the table method at the moment. )
Also the games will be categorized by console; how would I manage that? Say I wanted to make a list of codes for Mario Kart 64. Should I make a table named "N64_MarioKart64" or can I make an N64 table with the games as 'sub-tables'? |
|
|