#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0

    Need help creating table


    hi,i could not think any possible solution...so please help me...here is my problem..
    i have a table like this..(MONTHLY REPORTS)
    1. District name
    2. Month
    3. No.of total case register
    4. no of unspecificed case
    5. no. of suspected condition
    6. Species
    7. Breed
    8. sex
    9. name of disease condition
    10. total

    here is where i lost my self..
    Point/column 1 to 5 has to be enter once in a month and 6 to 10 can be any number of records for that month.
    point 5 is the number of records enter i.e. nos. of records enter for point 6 to 10.
    Also how to make constrains on the table so that reports can be enter monthly-wise. But a single month can contain multiple data.
    So how will i create the table structure...plz help me..i don't know any other possible solution(not at all).
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zorrs
    Point/column 1 to 5 has to be enter once in a month and 6 to 10 can be any number of records for that month.
    you need to split this up into two tables
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    you need to split this up into two tables
    If i split the table...how will i create the constraint for allowing records to be enter only once a month...because table 2 can contain multiple records for one month...plz help
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    If i split the table...how will i create the constraint for allowing records to be enter only once a month...because table 2 can contain multiple records for one month...plz help
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zorrs
    ...how will i create the constraint for allowing records to be enter only once a month
    this would likely be controlled by your application code

    but why restrict entry to only once per month?

    what if someone wanted to update some data today, and then some more data tomorrow?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    CREATE TABLE `adseras`.`monthlyreportindex` (

    `idmonthlyreportindex` INT NOT NULL ,

    `month` VARCHAR(45) NOT NULL ,

    `year` YEAR NOT NULL ,

    `district` VARCHAR(45) NOT NULL ,

    PRIMARY KEY (`idmonthlyreportindex`, `month`, `year`) );

    will this work...base on the composite key no duplicate month and year will be allow to insert into this table...Am i correct? and thanks for helping me
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    yeah, that'll work

    but why in the world do you have VARCHAR(45) for the month??
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    Originally Posted by r937
    yeah, that'll work

    but why in the world do you have VARCHAR(45) for the month??
    thank you and sorry about that...just wanna confirm that i will work..have to change it...
    just one question...
    table 1 (containing month,year,district)and table 2(the details) have to be enter at the same time(same form) so can you please suggest any method to do that...hope my question is clear..
    and thank you so much..it really help me
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zorrs
    table 1 (containing month,year,district)and table 2(the details) have to be enter at the same time(same form) so can you please suggest any method to do that...
    you should probably start a new thread in the forum corresponding to the application language (php?) which processes the form submission
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    SIR...one last question...there is some problem with my table

    CREATE TABLE `adseras`.`monthlyreportindex` (
    `idmonthlyreportindex` INT NOT NULL ,
    `idmonth` INT(10) NOT NULL ,
    `year` YEAR NOT NULL ,
    `iddistrict` (10) NOT NULL ,
    PRIMARY KEY (`idmonthlyreportindex`, `month`,'iddistrict" `year`) );

    i have made some modification..
    1. idmonthlyreportindex (auto increment)
    2. i also make both idDISTRICT and idMONTH as FK
    (all of them still Primary Key)
    I've tried inserting records to this table and still accepts duplicate data because of the auto increment idmonthlyreportindex.
    But i also need this idmonthlyreportindex to be the reference for the detail table(2) and i can't make idDISTRICT and idMONTH or year to be unique because it will be needed for other tables and say next year....
    please just this one i need your help again...i kindly beg you
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Code:
    CREATE TABLE adseras.monthlyreportindex 
    ( idmonthlyreportindex INTEGER NOT NULL PRIMARY KEY
    , iddistrict INTEGER NOT NULL 
    , idyear YEAR NOT NULL 
    , idmonth TINYINT NOT NULL 
    , UNIQUE ( iddistrict , idyear , idmonth )
    )
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2011
    Posts
    15
    Rep Power
    0
    Thanks you so much..it works find...
    Can you please explain this concept...do you combine iddistrict , idyear , idmonth as one and make it to be unique? or is this composite key..
    just want to be clear on this concept if you don't mine explaining it..
    Thanks you
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by zorrs
    ...do you combine iddistrict , idyear , idmonth as one and make it to be unique? or is this composite key..
    the combination of values of the three columns must be unique

    it is a composite key, yes
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo