Need help with a table and a sub table

Results 1 to 3 of 3

Thread: Need help with a table and a sub table

  1. #1
    Join Date
    Dec 1969

    Default Need help with a table and a sub table

    Can anyone help me. I am having a major brain cramp. I have built a database that has 2 tables in it, a main_project&#039;s table and a sub_projects table. In the main table I have 3 fields, ID,DATE, and USERNAME. I have the DATE and USERNAME as Primary Keys. In my Sub table I have ID, USERNAME, LOCATION, HOURS, and COMMENT. I have ID and USERNAME primary keys. I have a relationship set up between these too but when I go to enter in a user twice I get an error saying that I can not have duplicate, when I change it to accept duplicates the database doesnt work. I am needing to have a database where a user can enter in multiple location, hours, comment for a single date. I dont know what I am missing.<BR><BR>Any help would be great.<BR>

  2. #2
    Join Date
    Dec 1969

    Default Uh, you answered your own question

    "I have ID and USERNAME primary keys" when talking about your Sub table. If ID and USERNAME are not unique for each and every record, you can&#039;t do the insert.

  3. #3
    Join Date
    Dec 1969

    Default You've denormalized your tables

    The second table should *NOT* have the USERNAME field in it.<BR><BR>I think this is what you are after:<BR><BR>PROJECTS:<BR> projID : primary key <BR> Username : text or varchar<BR> Date : datetime<BR><BR>SUBPROJECTS:<BR> subID : primary key<BR> projID : *foreign key* to the Projects table!!<BR> Location : text or varchar<BR> Hours : double or integer<BR> Comment : text or varchar<BR><BR>I wouldn&#039;t make the combination of projID and UserName the Primary Key in the projects table (you could, but it doesn&#039;t buy anything). I think the projID alone is sufficient.<BR><BR>And, actually, if you have other places where you duplicate UserName then it should be moved to its own table. So better would be:<BR><BR>USERS:<BR> userID : primary key<BR> UserName : text/varchar<BR> UserPhone : ...<BR> ... lots of other stuff ...<BR><BR>PROJECTS:<BR> projID : primary key <BR> userID : foreign key to USERS table!<BR> Date : datetime<BR><BR>

Posting Permissions

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