Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Post Unanswered: Updating table from another tables

    Hey guys, I have a problem with my website, well I have a sql database 2005 with bunch of tables, I got ), faculty_profiles (see figure 1), cat_course (see figure 2), cat_lessons (see figure 3) and bunch of asp pages, first faculty fill out my registration form and then submit it to faculty_profiles table (access_profile, access_image and access_resume are hidden fields in my registration form with a default value which is Waiting, after registration and login to their accounts they can add lessons by submitting a form to cat_lessons table (when they login to their accounts I use a session variable to grab their username when they login and then after login and going to submit lesson form I use a recordset (faculty_profiles) and filter it by that session variable to find the exact record that matches the username that faculty use to login so I reach (access_profile, access_image and access_resume values which by default are Waiting and make them hidden fields again to submit to cat_lessons), and for course title I use a recordset for cat_course table and let the faculty choose their course and finally submit their lessons.

    Now the problem is if faculties submit lessons and then some time later upload photo and resume or change their personal information everything changes in their record in faculty_profiles table but nothing happen in cat_lessons table and everything is fixed for example imagine a faculty insert a new lesson to cat_lessons so access_image and access_resume values are Waiting and then next week he/she upload their resume and then access_resume turn to Accept and will update the value Waiting to Accept but this only happen in faculty_profiles table not in that many rows that submitted by that faculty so those rows in cat_lessons (maybe hundreds of those before updating faculty_profile) so they should be updated to the new value in cat_lessons just like faculty_profiles, same for cat_course table (there is a possibility that admin update course title) then course title in cat_lessons should be as exact as cat_course table.
    Attached Thumbnails Attached Thumbnails cat_faculty ( figure 1).jpg   cat_course (figure 2).jpg   cat_lesson (figure 3).jpg  

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I don't understand everything you wrote. Based on
    will update the value Waiting to Accept but this only happen in faculty_profiles table not in that many rows that submitted by that faculty so those rows in cat_lessons (maybe hundreds of those before updating faculty_profile) so they should be updated to the new value in cat_lessons just like faculty_profiles
    I think you have a problem due to a faulty normalisation. But that is hard to tell if you only give part (3 tables, without relationships) of your data model.

    You wrote about the "faculty_profiles" table, but it is not present in the tables you attached. Can you submit that part of your data model that is of interest? Best with the relations between the different tables.

    Your explanation is hard to follow. Can you give example data in a few tables. Then the data that is present after their update. And the data you would want to be in the tables?
    Some good example data is often far better to explain your problems to people who really have nu clue what your business does, what you have been working on for weeks and what your problem really is.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    Quote Originally Posted by Wim View Post
    I don't understand everything you wrote. Based on I think you have a problem due to a faulty normalisation. But that is hard to tell if you only give part (3 tables, without relationships) of your data model.

    You wrote about the "faculty_profiles" table, but it is not present in the tables you attached. Can you submit that part of your data model that is of interest? Best with the relations between the different tables.

    Your explanation is hard to follow. Can you give example data in a few tables. Then the data that is present after their update. And the data you would want to be in the tables?
    Some good example data is often far better to explain your problems to people who really have nu clue what your business does, what you have been working on for weeks and what your problem really is.
    You're right so let’s make it more visual to clarify it a little bit more,

    Let’s imagine John Smith want to be one our instructors so we lead him to a page with a registration form in it.

    Now he fill out all the necessary information and he then submit the form (when he submit the form he submit some hidden input alongside of the other visible inputs and, after submitting form all of its data’s will save in a table named faculty_profiles as its shown below in (Figure 1)

    After that he could login into his account by entering his email (as username) and password when he do that a session variable named MM_username would save that email and later on I use that session var to filer a recordset that I created to have all john’s registered informations including those hidden fields in the previous paragraph and its names and other etc… then I use that recordset in a page that john could submit a new lesson, he’ll fill out a form containing all necessary informations like course title and etc and bunch of hidden fields including his name, resume address, image address, access_profile, access_image an daccess_resume (all of these hidden inputs value feed by that recordset which I earlier created and filtered by MM_username) so he submit that form into a table named cat_lessons (See Figure 2)

    Now in user interface of the website people see that john added a new course , john will add other courses as well maybe up to 50 then someday all the sudden john decide to add a new resume and picture so he do it then update his profile :

    After that he’s going to add new lessons again but his previous lessons still use the old information cause it is stored in table (cat_lessons) but john updated the table (faculty_profile),

    (if he added 50 lessons to table cat_lessons before he update his profile which will update a specific record in faculty_profiles table which related to john so all of those 50 lessons in table cat_lessons will follow john new info and update themselves based on what happen in table faculy_profile) Question is how I can update all those records in cat_lessons that related to previous john informations including everything that used in hidden field to submit to cat_lessons).

    Simply all john's lessons in cat_lesson table which have a name, resume, image and other fields similar to faculty_profile table will follow john update profile that update a record with ID that is for john even if there hundreds lessons belong to john in cat_lesson all of those fields will update to johns new information in faculty_profile.

    So if he submit a new lesson with the name of john then he changes his name to Jonathan that course instructor name should change to Jonathan as well which means updated happen in faculty_profile table but had an effect on john name in cat_lesson table and updated it to Jonathan all off his added course in cat_lesson will change to Jonathan.

    Can we use SQL UPDATE statement? I need clue and an answer which I currently don't have.
    Attached Thumbnails Attached Thumbnails (Figure 1).jpg   (Figure 2).jpg  
    Last edited by datis; 09-11-11 at 11:25.

  4. #4
    Join Date
    Sep 2011
    Posts
    3
    Any Suggestions?

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Your data model is clearly not-normalised.
    Remove all the columns from cat_lessons that also occur in faculty_profiles. (profile_image, first_name, Last_name, Profile _resume, ...)

    When you need the name, image, ... of the teacher, you will have to JOIN the two tables by ID (I think, you did not gave the relations between the tables) and use the columns present in faculty_profiles (profile_image, first_name, Last_name, Profile _resume, ...).

    You have duplicate data in your database. It is hard to keep those synchronised, as you have found out. That is one of the goals of normalisation: to remove all duplicate columns from your data model.

    You seem to have a limited knowledge about normalisation and data modelling. Read a good book about it before continuing. Using a normalised database is easy. Using a badly designed database is a nightmare.

    Normalising a database is not difficult, but is is something that you have to learn and apply.
    You are now building the very base of your application. If that base is shaky, your whole application will be.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •