Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2010
    Posts
    1

    Thumbs up Unanswered: Urgent!! Please tell me How to Create a Table

    Dear friends,
    I want to create one table with some columns. In front End Application I designed 3 dropdownlist controls. First dropdownlist should be filled by default. Based on what we selected in first dropdownlist, then values in second dropdownlist be loaded. Based on what we selected in second dropdownlist, then values in third dropdownlist be loaded. The data loaded from database and that too from a single table. This is the requirement. Please tell me how to create such table in SqlServer. As I recently joined in company, I am fully troubled with work. Please help me.

    Requirement is something like below.

    First Dropdownlist==> All Country Names

    Second Dropdownlist==> All state names based on what country we selected in First Dropdownlist.

    Third Dropdownlist==> All Districts based on what state we selected in Second Dropdownlist.

    All the data should come from single table in sqlserver.

    Please Help me. Its very urgent requirement. Please support me in developing my career.

    Thanks & Regards,
    Cheguri.


    Thanks.
    Last edited by cheguri; 04-28-10 at 02:31.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you are looking for some actual code examples, you will need to supply the layout of your table(s).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Apr 2010
    Location
    Charlotte, NC
    Posts
    11

    Talking

    Good Afternoon, let's see if I can help.

    "All the data should come from single table in sqlserver." This would be a bad design since the three pieces of data are related, but not 1 to 1. To build the table you've requested you would use:

    CREATE TABLE CountryStateDistricts (
    countryName varchar(255)
    , stateName varchar(255)
    , districtName varchar(255)
    )

    You will have to enter the same countryName 50 times to enter all the stateNames for america. Then in order to enter all the districts, you'd have to enter duplicate country and states for each district in that state. Lot's of hard work.

    Then to get the drop down lists back out again, you would have to:
    SELECT DISTINCT countryName FROM CountryStateDistricts
    Then to get the States you'd have to:
    SELECT DISTINCT stateName FROM CountryStateDistricts WHERE countryname='selectedCountry'.

    If you designed three separate tables, each with a Foreign key to relate to it's "parent" table, you would reduce the duplication, and make it easier to query, and easier to maintain.
    My suggestion would be

    CREATE TABLE country(
    countryID INT IDENTITY(1,1)
    , countryName VARCHAR(255) )

    CREATE TABLE state(
    stateID INT IDENTITY(1,1)
    , countryID INT --I would add a constraint on this column
    , stateName VARCHAR(255) )

    CREATE TABLE district (
    districtID INT IDENTITY(1,1)
    , stateID INT --again use a constraint here
    , districtName VARCHAR(255) )

    This method of development has to do with normalizing your data. It's a concept you may want to learn more about, but I won't bore you with the details here.

    Let me know if you need further assistance on this task.
    ---
    Shannon Lowder
    Database Engineer
    http://toyboxcreations.net

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Shannon Lowder nailed it! What you are looking to design is often referred to as noramalization.

    I would suggest following Shannon Lowder's advice and normalizing your design as suggested, then query those tables and you'll have the results you're looking for.


    I couldn't resist. Let somebody catch up with Rudy anyway.
    Dave

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by dav1mo
    nailed it!
    Rudy would be so proud
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dav1mo View Post
    ...I would suggest following Shannon Lowder's advice and normalizing your design as suggested
    sorry, but the table as presented ~is~ normalized, assuming that the PK is actually declared...

    CREATE TABLE CountryStateDistricts
    ( countryName varchar(255) NOT NULL
    , stateName varchar(255) NOT NULL
    , districtName varchar(255) NOT NULL
    , PRIMARY KEY ( countryName, stateName, districtName )
    )

    if you feel that this is not normalized, please explain exactly which rule it violates (1NF, 2NF, etc.)

    please note that normalization does ~not~ mean "use surrogate keys"

    nor does normalization mean "remove repetition" (because the values of the surrogate keys repeat just as often as the natural keys they replace)


    i am afraid the "nailed it" accolade is incorrect

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    i am afraid the "nailed it" accolade is incorrect

    We'll have to confer before we release a decision on what impact this has on the scoring.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Ah, but in Shannon's method it does not repeat the country value for each district within a state. So, in a case such as the U.S., the country would be repeated 50 times in the state table, but not the hundreds, thousands, millions??? of times in the district table.
    Dave

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dav1mo View Post
    Ah, but in Shannon's method it does not repeat the country value for each district within a state. So, in a case such as the U.S., the country would be repeated 50 times in the state table, but not the hundreds, thousands, millions??? of times in the district table.
    Dave
    so?

    how exactly does that affect whether it is normalized or not?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Because Country is superfluous if you have State, as Country can be derived from State. It is therefor unnecessary to include it in the table, and gives rise to possible data corruption if a State is assigned to two different Countries.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    I would think in Shannon's method that if you needed to update a name of a country or state for some reason, you would make the change 1 time instead of possibly hundreds or thousands....thus making it easier to maintain the data integrity.
    "Things are only impossible until they are not." ~Jean Luc Picard

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Rudy would likely respond with "How often does a Country or State change it's name"? And honestly, would updating a hundred records, or even a thousand records, really require much processing time on a modern server?
    Relational integrity is the key issue. Speed of updates on what is essentially a lookup table is nearly moot.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman View Post
    Because Country is superfluous if you have State, as Country can be derived from State. It is therefor unnecessary to include it in the table, and gives rise to possible data corruption if a State is assigned to two different Countries.
    Otherwise known (in other terms) as BCNF.
    I am going to bet that Rudy's actual response will be that states are not uniquely named throughout the world (within countries yes, between them no) and so you can't derive country from state.


    This is so exciting - I can't wait for the next instalment!

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I bet Rudy's response will be to recognize the correctness of my genius, and also revise his book to include a chapter on why gratuitous aliasing of table names is a bad practice.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by blindman View Post
    the correctness of my genius
    Genius has degrees of correctness now? Who knew?

Posting Permissions

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