LINKING TWO mysql TABLES

michael1291

Hi i m trying to link two mysql tables based on member_id, i have a members table with the primary key of member_id but i need another table to have the same primary key however i need to populate the column with the same data stored in the members member_id column. and each time a new row is added to the members table a new row should be added to the areas table.

The only reason i am having separate tables is that there are over 70 columns i need to trying to split it.

I need to use the same id as when i update the table it needs to update depending on member_id

bradgrafelman

Can you show us the structure of the two tables?

michael1291

my members stable:

members_id, firstname, lastname, address, postcode, email, phone etc

and

areas:

member_id, area(followed by the area code e.g SR) i have up to 119 colums for area codes.

IS THIS WHAT YOU MEANT BY STRUCTURE?

bradgrafelman

michael1291 wrote:
there are over 70 columns

michael1291 wrote:
area(followed by the area code e.g SR) i have up to 119 colums for area codes.

70 columns? 119 columns? Yikes! Sounds like what could be a very, very poorly designed DB schema! :o

michael1291 wrote:
IS THIS WHAT YOU MEANT BY STRUCTURE?

Oi, easy with the caps lock there. But yes, that is, more or less, a basic description of the table structures (feel free to just post the full 'CREATE TABLE..' queries, though).

While what you're asking could certainly be accomplished with triggers and whatnot, can I first ask... what is it you're trying to do? Any chance you could given us a quick summary of what you're using your database for?

michael1291

Sorry about the caps lol,

Right firstly I have a members area, they login and can view invoices and information specifically for them as an individual, then they can update the areas they are currently operating in to inform the administrator, they do this by selecting a check box but there are like 119 areas to choose from, I orrigionaly planned on saving the areas they select in one field as they would only select a few areas at a time but struggled to retrieve the checkbox value to select the checkbox they are operating in. Do you think it is s bad ideanto have this many columns ? Will it affect performance ?

Would you know how to retrieve the value from a single field to then check a check box, I had it working up to a point where it would check it if a any value existed but because the value was entered by array and separated by a comma I could not achieve this, thanks for the reply by the way.

bradgrafelman

michael1291 wrote:
I orrigionaly planned on saving the areas they select in one field as they would only select a few areas at a time but struggled to retrieve the checkbox value to select the checkbox they are operating in.

That sounds like you're wanting to create a multi-valued field. If so, don't (and erase that notion from your thoughts permanently :p). The first normal form states that you have no multi-valued fields (or "columns" or "attributes" or whatever you want to call them).

michael1291 wrote:
Do you think it is s bad ideanto have this many columns ?

I think it is a bad database design, yes. All of the following statements hold true, and none of them belong in a proper DB schema:

Modifying existing areas means you would have to modify the actual table schema.
Adding new areas means you would have to modify the actual table schema.
Deleting old areas means you would have to modify the actual table schema.
Your SQL queries will probably be very large simply because you've spread out related data across a large number of columns (e.g. all of the columns are describing the same thing or idea... just specifying a different value).

michael1291 wrote:
Will it affect performance ?

I would say yes, it will.
If user1 operates in 2 areas, the corresponding row in your DB will still contain null/empty/etc. values for the other ~117 areas.

Personally, I would create a separate reference table that maps the many-to-many relationship between users and areas. It would have only two columns - (user_id, area_id).

Going back to user1 (where userN describes a user with an ID of N), and assuming the two areas he was working in had IDs of 1 and 2, you'd see two tuples in the reference table: (1, 1) and (1, 2).

In other words, a user would have one row per area worked in. It's a lot more efficient/compact, and, in my opinion, it's a heck of a lot easier to work with SQL queries dealing with only 2 columns than ones dealing with ~119 columns.

Plus, modifying/deleting/adding new areas does not require any schema modifications - just regular UPDATE/DELETE/INSERT queries.

michael1291 wrote:
Would you know how to retrieve the value from a single field to then check a check box

That would largely depend upon: a) how the data is stored in the DB, and b) how the data in the DB is related to the checkbox(es).

michael1921 wrote:
I had it working up to a point where it would check it if a any value existed but because the value was entered by array and separated by a comma

In other words, you had a multi-valued field? See my first response above. 😉

Derokorian

If this was Facebook... I would definitely hit like on that post brad =D