im making an online address book using a database (mysql) and i have a few question about it. one, i have everything made and working almost 100% but it seems very inefficient, uses way too much code that does almost the same thing. i have a database called "address" and four tables. they are: `names` => ID, FIRST_NAME LAST_NAME `location` => ID, CITY, STATE `contact_info` => ID, EMAIL, PHONE `date` => ID, DATE all of the IDs are primary keys and i dont have any unique keys or the other ones (i dont really understand what they are, i just made the IDs primary because i red a little in a book about them). well... my question is. is that how i should be setting up my database? if not how? and secondly, the reason i dont think it's that way is because when i try to get all of the information from one person (ID) it doesnt work. it says it's ambiugous. SELECT * FROM `names`, `location`, `contact_info`, `date` where `ID` = 2 (for instance) i would greatly appreciate any help on this. thanks FBI Agent AIM: EliteAssassin187

There is too much info to go into proper dsatabase structure or to teach you how to properly form databaser queries here - at least it would take more time than I am willing to devote. The main problem I see is that you have four tables. Why? The only reason to do that (in this situation) is if the values in one table would have a many-to-one relationship witht he values in another table. Are you expecting that people will have multiple addresses, or multiple emails? It's entirely possible, but if this is your first project I would keep it simple and have just one table. Then you just need to Select * from "table" The reason your query doesn't work is that you need to join the tables. For instance the ID last three tables should match up with the releveant ID of the names table or they should include a namesID column. Then in your select statement you need to include a JOIN statement that joins the tables based upon the common ID.

Michael's right on in saying this should all go into one table, unless you are trying to account for many to one or many to many relationships. To be clear, the reason your query doesn't work is that you are joining the tables, but you aren't filtering the results. When you join two tables (which happens when you list them after the select statement), the result is every possible combination of rows from each table. For example, if you had two tables, A and B, both with one field named ID, select * from A,B this is what would happen: A  B 1  1 2  2 select * from A,B: ID  ID 1  1 1  2 2  1 2  2 So you need to filter the results by specifying that you only want the ones where the id is the same. But if you just say WHERE id=1, the DB doesn't know which ID field you're talking about, since there are two. What you really want to do is WHERE A.id=B.id AND A.id=2. Good luck, -SN

ohhhh. i get it SN. that's pretty sweet. sounds like it'll work. im still confused on how the JOIN works though. and no micheal, this isnt my first project using MySQL, it's my second ;) i think that i did very good on my first project though, didnt have any problems but i only used two tables but they were used separatly from each other and i didnt add too much to it. so now that i know how i could be doing this, should i really put this all in one table? on one query that i have it only needs the data from the `names` table, would it be more efficient to just use one table and take it all from there or just take it from three different tables? thanks for the advice the both of ya ;) FBI Agent AIM: EliteAssassin187

SELECT * FROM `names`, `location`, `contact_info`, `date` WHERE `names`.ID = `location`.ID = `contact_info`.ID = `date`.ID AND `names`.ID = 2 lmao, that's one big ass query. thanks SN ;) but still let me know about that other question FBI Agent AIM: EliteAssassin187

"would it be more efficient to just use one table and take it all from there or just take it from three different tables?" When I was first learning about databases, I was obsessed with efficiency. Honestly, forget about efficiency - that's a complicated topic. Once you know SQL, the RDMS you are using, etc. you can think about that and will know enough to handle it. For now, focus on how you want to access your data - what queries you will want to perform. In your case, the data is simple. Breaking up across tables doesn't make querying any more or less difficult. Is there a query you can't do with one table? Assuming everything else is the same, querying many tables does not give you any advantages over querying one. Or maybe it does, I don't know what you are doing with the data. When you have a dataset, think about what you want do with it. After a few projects, you will understand SQL enough to know when to break up tables, because it will be obvious. A book on the basics of normalization and ACID will show you when to do this. Look for some complicated datasets and try to query useful information. Once you see the relationships with real data, things will make a lot more sense.

well the thing is, i can do it either way, with one table or more. the thing is, sometimes i just need to extract the data from only one table (meaning it would be faster, right?) instead of takeing all of the data... but now that i think about it, i can put it all inot one table and then extract only certain parts, but then that makes big long complicated queries. i think i may do it this way for now and then think about it more later and then see how i would like it if i used one table and queries from there. thanks for the bit about efficiency. it's just hard fr me to not think about that, i always write my code to the best of my ability and if i dont know the best way to do something, it really messes with me because im doing it 'wrong'. FBI Agent AIM: EliteAssassin187