Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I am currently working on a research, uhmm, prolly will create an application out of it, the system is an "intern management system" . My question is how will you structure your database if you are in my shoes?

but before answering, take a look at this, this is what's on my mind

table : users
fields :
   user_id, username, password, firstname, middlename, lastname

table : roles
fields :
   role_id, role_name

table : *user_role*
fields :
   user_id, role_id

foreign keys:

  • user_id reference users(user_id)
  • role_id reference roles(role_id)

table : status
fields :
   status_id, status_name

table : *user_status*
fields :
   user_id, status_id

foreign keys:

  • user_id *reference* users(user_id)
  • status *reference* status(status_id)

the above is the current structure of my database, any suggestion on the above? how about if i am going to add the users a field of college and course

if he is on college of engineering, or in college of business ad . . and so on and so forth, the college of engineering has many courses within it, how do i handle that kind of situation, should i just create a table called colleges, courses, then college_courses and lastly a table called user_college_course? what do you think?

share|improve this question
It is important to state what the system is expected to do and who does what. For example, why do you care about courses for a given user? Do you want the system to list all users who studied Calculus? Do you care if someone studied Calculus at UBC or UCLA? What is the difference between a "user" and an "intern"? Please provide more specific requirements. –  Emmad Kareem Aug 19 '12 at 14:24
i see. . . thanks for the advice! –  Joey Salac Hipolito Aug 19 '12 at 14:28
add comment

closed as too localized by MainMa, gnat, Jalayn, Walter, JeffO Aug 20 '12 at 19:18

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

up vote 0 down vote accepted

colleges, courses, then college_courses and lastly a table called user_college_course?

This looks good, Assuming a course can be offered at more than one college.

Same Question for the user_status table, can a user have more that one status? If yes, you need a filed to specify what the Status is of.. (and maybe another Table Status_Type)

share|improve this answer
uhmm, i think a course can only be offered on one college, example bs computer engineering can only be offered in college of engineering and nowhere else. i think the term 'course' is a bit confusing. –  Joey Salac Hipolito Aug 22 '12 at 17:08
so you don't need the college_courses table, just add a field for Collage on Courses table. –  Morons Aug 22 '12 at 18:53
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.