DB structure needs to be changed up!

[codeline]

Alright guys.. I need some advice, some recommendations and definitely some help.

 

I've got a submission form for users to upload an image and some basic personal information. The client wanted the information to be divided by the North America (specifically N.A. and not U.S.) and Europe.. so I created a database table with the typical structure: a row for the user's name, email, etc. I also added a row labeled "regionid" in which "1" would equal North America and "2" would equal Europe. This "regionid" row would obviously organize the submission by their country.

 

The client specifically wanted to label North America but mentioned only users from the States would probably only submit images, etc.

 

Users could choose if they were submitting a form for US or Europe and "regionid" would be filled by which form was chosen.

 

 

Recently, the client threw a curveball and now wants Canada as part of the North America submission form. Now, I don't want to add Canada as another option for my "regionid" row because it does belong under North America. However, I need to alter the structure of the database I currently have so that it can now organize if under North America or Europe, and if under North America, if the submission falls under the United States or Canada.

 

I was thinking of adding a drop down menu under the North America form with the options US or Canada.

 

Do you guys have any advice or other directions you would take to solve this problem.. that is, if my situation is even understandable? I found it pretty difficult to explain! Apologies!

 

 

 

 

[attachment deleted by admin]

[ignace]

Likely your customer is going to keep adding as the project grows. Look into an Adjacency List:

 

CREATE TABLE region_adjacency (
  parent_id TINYINT UNSIGNED NOT NULL,
  child_id TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (parent_id, child_id)
);

CREATE TABLE region (
  id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ..
  PRIMARY KEY (id)
);

 

Something like that.

[codeline]

So correct me if I'm thinking this wrong but you would suggest creating another table that works directly with the "regionid" row I have in my current submissions table?

 

Then from there, I would be able to add additional rows (as in your example: child_id) to specify US or Canada, etc.?

 

I'll look into the Adjacency List deal..

 

Thanks!

[ignace]

Yup, you would add Canada under North America

 

INSERT INTO region (id, ..)
VALUES (1, 'North Ameria'), (2, 'Europe'), (3, 'Canada');

INSERT INTO region_adjacency (parent_id, child_id)
VALUES (1, 3);

[codeline]

Alright, that doesn't sound too bad! What about my form design?

 

I currently have a specific form for users to submit for either NA or Europe. Within the NA form I have a City input and State input (State input is required). I was thinking of putting a drop down menu right above those to choose whether the user wanted the US or Canada. However, if they chose Canada, the State input wouldn't be necessary.

 

How would you go about changing required fields depending on whether a user selects a certain drop down select item?

[codeline]

@ignace

 

So what would be the best route to call out that information when I want to display submissions based on country?

 

I'm getting a bit confused since I am now working with 3 tables (submissions, region, region-adj). My 'submissions' table has a 'regionid' row that accepts '1' (North America) or '2' (Europe). My 'region' table has the values '1' (North America), '2' (Europe), and '3' (Canada). My 'region-adj' table has 2 rows (parent and child) and values ('1' and '3') for North America and Canada.

 

My mind is a bit clustered on how I would query information based on the 'regionid' row in my submissions table, then if the value of 'regionid' is '1', showing the child country, which could be United States or Canada.