current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 4 down vote favorite

I need advise whether my database structure is correct and what is the appropriate query syntax that can be used.

Table1

╔═══╦════════════╦══════════════╦════════════╦═════════╦════════════╦══════════╗
║Id ║Country_id  ║   Country    ║   City     ║  Grades ║ College_id ║Subscribed║
╠═══╬════════════╬══════════════╠════════════╬═════════╬════════════╬══════════╣
║ 1 ║  US        ║ United States║    NY      ║   12    ║     32299  ║    1     ║
║ 2 ║  US        ║ United States║    LA      ║   12    ║     32299  ║    0     ║
║ 3 ║  CA        ║ Canada       ║    ON      ║   13    ║     32299  ║    1     ║
║ 4 ║  CA        ║ Canada       ║    ON      ║   11    ║     32299  ║    1     ║
║ 5 ║  MEX       ║ Mexico       ║    DF      ║   12    ║     32299  ║    0     ║
║ 6 ║  MEX       ║ Mexico       ║    GUA     ║   12    ║     32299  ║    1     ║
╚═══╩════════════╩══════════════╩════════════╩═════════╩════════════╩══════════╝

Table 2

Where TableName = College_id ( 100 Tables with same Data structure)

╔═══╦════════════╦══════════════╦════════════╦═════════╦══════════════╦
║Id ║College_id  ║   Student    ║   Phone    ║  Grades ║ College Name ║
╠═══╬════════════╬══════════════╠════════════╬═════════╬══════════════╣
║ 1 ║  32299     ║ Jhon         ║  123-456   ║   12    ║     UNV      ║
║ 2 ║  32299     ║ Eric         ║  123-457   ║   12    ║     UNV      ║
║ 3 ║  32299     ║ Martha       ║  123-459   ║   13    ║     UNV      ║
║ 4 ║  32299     ║ Steve        ║  123-458   ║   11    ║     UNV      ║
║ 5 ║  32299     ║ Gloria       ║  123-451   ║   12    ║     UNV      ║
║ 6 ║  32299     ║ Justin       ║  123-455   ║   12    ║     UNV      ║
╚═══╩════════════╩══════════════╩════════════╩═════════╩══════════════╝

I need help with the query syntax using the following criteria :

$table = Dropdown box Selection = College_id

$CountryId = Dropdown box Selection = Country_id

Select STUDENT, PHONE FROM $table WHERE :
College_id + Grades ( From table 2 )  = College_id + Grades (From Table 1) 
AND Country_id = $CountryId 
AND Subscribed = TRUE
share|improve this question

migrated from stackoverflow.com May 20 '13 at 18:36

This question came from our site for professional and enthusiast programmers.
7  
Nice table drawing, I'll give you that :P –  Madara Uchiha May 20 '13 at 17:32
    
What have you tried ?? Try yourself and post if you have any problem. Nice table design though. –  Vivek May 20 '13 at 17:34
    
I am not sure if it is a correct approach to combine two fields values like where field1 + field2 in table 1 = field1 + field2 in table 2 ... thanks –  Raja Khoury May 20 '13 at 17:37
1  
there's a separate table for every single college in your database? you might want to consider having one "College" table and storing all the related information in it, especially since they all have the same exact structure –  ChrisCamp May 20 '13 at 17:38
    
Yes, every college_id = one table.. I think you are right I wanted to have all within the same table, but I was thinking in the long term to maintain these tables.. I guess I'll use one table –  Raja Khoury May 20 '13 at 17:40

2 Answers 2

up vote 2 down vote accepted

You will have a much harder time trying to maintain 100 tables with identical table structure then you would if there was just one table w/ all College related information in it. If you are concerned about the long term be sure to look into ways to optimize access to the table through the use of table indexes and efficient query writing.

new sql statement (assuming that table1.id and table2.id has a foreign key relationship)

Select STUDENT, PHONE 
FROM Table1, Table2
WHERE Table1.id = Table2.id
AND Table2.College_id + Table2.Grades = Table1.College_Id + Table1.Grades
AND Country_id = $CountryId 
AND College_id = $table -- no longer a table, but instead a clause in your where  
AND Subscribed = 'TRUE'
share|improve this answer
    
I will take your advise into consideration. Designing a good database is primordial for a successful app. –  Raja Khoury May 20 '13 at 17:56
up vote 0 down vote

As well as the one-table-per-college issue, your schema is denormalised and should be broken out into more tables. At the moment a particular college_id could have multiple college_names, which a correctly normalised schema would not allow.

You probably need a table for STUDENTS, one for COLLEGES, one for ADDRESSES,one for CONTACTS (holding phone numbers, email addresses, etc).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.