best php query method / setup for php/mysql

[programguru]

Hello All,

 

I know enough about php to get some decent work done, but I don't have enough solid experience to know what the best route for a particular purpose would be.

 

So that is why I am making this post. I will be breif and to the point with as many details as possible.

 

PROBLEM: I need to decide on the best method to structure my tables to support the script I am wrting.

 

INFO: I have 5 tables. Each of them contains a unique type of data, but all of the data will be related when queried:

 

-- person (5 fields - id, (auto increm), firstname, lastname, address, address2)

-- state (2 feilds - id, (auto increm), state)

-- city (2 fields - id, (auto increm), city)

-- sex (2 fields - id, (auto increm), sex)

-- moredata (2 fields - id, (auto increm), moreinfo)

 

I already have my data entry form complete.

 

Does this look like a decent structure?

 

I am in the middle of writing my query code, but I think there could be a more efficient way to do this for search purposes.

 

I will have a query function that starts with: state, and then will allow the user to select: city (cities associated with state selection), and then results will be displayed of people in that state/city combo.

 

My general quesion is. Is auto-increment "id" the best method to relate all of the queries data?

 

SOS!

[genericnumber1]

You shouldn't have that many tables, consider combining them all.

 

As for your question, no, auto increment (on all of the tables) isn't a good way to link them, and could even potentially lead to security vulnerabilities. To relate multiple tables, using yours as an example, you could add an extra field to the state, city, sex, and moredata tables (again, you shouldn't have this many) labeled "personID" or something similar, and save the person table's ID in the associated person's state, city, etc. This will allow you to merge tables on query and maintain assurance that your tables will be linked as you expect.

[programguru]

genericnumber1,

 

Thanks for that info. I see what you are saying about the tables, but I'm not sure I understand why they should all be in the same table. For future expandability isn't it wiser to have separate tables for each type of data?

 

Can you suggest a benefit to using only one table for all of this data?

 

Your info on the record ID like "personID" is exactly what I was looking for. I recall this method from some time ago when doing this before.

 

I would insert "personID" with every new record entered and use that to associate all of the tables when doing queries.

 

 

 

 

 

[genericnumber1]

I'm not sure what you mean for "future expandability". You can always add more columns...

 

The benefits to combining them are numerous including simplicity (inserting into 1 table is much simpler than dealing with 5, same with searching) and efficiency.

 

Compare a typically simple search for a person's name based on state.

 

SELECT name FROM people WHERE state='Texas'

 

compared to

 

SELECT people.name FROM people LEFT JOIN states ON people.id = states.personID WHERE states.state = 'Texas'

 

Then I challenge you to fathom how complicated it would be to search a name by city AND state (3 tables involved) or even include city, state, and sex (4 tables).

 

That isn't to say you shouldn't always combine tables, but when you're doing something like a bio on a person, it's much easier to combine such similar fields into one table.

 

Also, efficiency, query 1 above would be faster than query 2.