Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Join Date
    Jun 2012
    Posts
    8

    One-to-Many Entities with One Column

    I need to represent three entities: Region, Sub-Region, and Country. With the relationship being one-to-many, my initial thought was to create three separate tables. However, the system only needs the (region, sub-region, & country) name. So, each table would only have one column, name (plus country and sub-region would have a FK).

    Would it be better to have just one table? I suppose the disadvantage is that sub-region and region would be duplicated in the table (but changes would be infrequent). The advantage seems to be simplicity of querying.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, go with just one table

    why do you say that region and subregion would be duplicated?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2012
    Posts
    8
    If using one table, I will have entries such as

    Region,SubRegion,Country
    Americas,Northern America,Canada
    Americas,Northern America,United States of America
    Americas,Northern America,Greenland
    Americas,Central America,Costa Rica

    Region and SubRegion are duplicated in the table (please see United Nations Statistics Division- Standard Country and Area Codes Classifications (M49)).

    With that, does it still make sense to use one table?

    I also wanted to note that I have another table that will be named Company. The company region, subregion, and country needs to captured. This is the only table that uses region, subregion, and country. Would it be even better to not have a separate table(s) and add three columns (region, subregion and country) to the Company table?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    by using one table, i assumed you would also be using a foreign key, as you had indicated for the three tables

    what you have is simply a three-level hierarchy, and the most common method for implementing that is with the adjacency model or tree, where every row has the FK pointing to its parent, or else NULL for root nodes

    see Categories and Subcategories

    however, back to this example...

    you mentioned a table like this ...

    Region,SubRegion,Country
    Americas,Northern America,Canada
    Americas,Northern America,United States of America
    Americas,Northern America,Greenland
    Americas,Central America,Costa Rica

    that actually works ~really~ well, as long as you are comfortable with whatever procedure you put in place to maintain the entries

    how many rows would this entail? a few hundred

    so it's ~very~ doable

    Quote Originally Posted by logicg8 View Post
    Would it be even better to not have a separate table(s) and add three columns (region, subregion and country) to the Company table?
    with the table as just mentioned above, all three columns would comprise the primary key

    then you'd have those three columns in the company table as a composite foreign key

    that way, you can control which combinations are allowed

    if you don't care, then judt drop the table and keep the three columns in the company table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Gotta disagree.
    (I seem to be disagreeing with Rudy a lot these days....)
    Either use a fully extendable adjacency model, with a single (non-composite) foreign key in the Company table, or use a simpler three table model with foreign keys between them and, once again, a single non-composite foreign key in the company table.

    You can cut corners by trying to simplify your model to fewer tables, but then you will end up with a data model that reflects your current requirements rather than reflective the actual business model. By making just the little extra effort in your initial design necessary to actually model reality, you'll be better prepared to handle changes to requirements down the road.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    You can cut corners by trying to simplify your model to fewer tables, but then you will end up with a data model that reflects your current requirements rather than reflective the actual business model.
    the actual business model is countries, organized into regions

    dude, how many countries are there?


    Quote Originally Posted by blindman View Post
    By making just the little extra effort in your initial design necessary to actually model reality, you'll be better prepared to handle changes to requirements down the road.
    changes to requirements... wtf?

    like, say, we no longer want to track companies by country?

    how about by prefecture?

    i want to be among the first to welcome our new planetary overlords, the gods of prefecture
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Quote Originally Posted by r937 View Post
    dude, how many countries are there?
    The answer is the ever popular: It depends.

    If you use the UN GO list, there are 192 countries.

    If you use the ISO list, there are 164 coutries.

    If you use the current Russian Federation list, there are 243 countries.

    All of these are subject to change at any time, based on the whims of diplomats (which are by definition heavy duty politicians, so stability is very questionable)!

    Have a good time resolving that mess, it is a quagmire I'm not willing to wade into right now!

    Quote Originally Posted by r937 View Post
    how about by prefecture?
    There are currently 47 prefectures in Japan alone... I don't want to dive into this quagmire either.

    Quote Originally Posted by r937 View Post
    changes to requirements... wtf?
    Requirements and specifications are roughly as stable as the people requesting them. As they learn more about what they originally requested, this almost always evolve furiously. It is the nature of human endevor.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    As they learn more about what they originally requested, this almost always evolve furiously.
    and your point is... ?

    i still say the single table is by far the easiest to administer here, in this situation, given that it's so easy simply to upload a new version of the regions/subregions/countries table from, say, a flat file or excel sheet

    all your lovely remarks, pat, about changing requirements are, of course, quite true

    but i'll be you a beer that they don't apply in this case
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Single table is a shortcut.
    Shortcuts invariably cost more in the long run.
    Three entities suggests three tables, or a single self-referential table (adjacentcy model or nested set).
    Plus, composite foreign keys play havoc with cascading updates/deletes.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    personally I'd go with a single table, as it is extendible without further design changes.
    because there is no single model that fits all such geographical data out in the world I don't see how a single table per level is going to work.

    ferisntance
    the US is comprised of 50 states, dropping down to god knows how many counties per state
    the UK is comprised of 4 countries, dropping down to counties (or Local Government administration boundaries
    arguably the UK & US are broadly similar except for the terminology
    a geographical model at 3 levels may work for most countries but it won't give a world fit.

    France has departments, but not all departments are in Europe

    If you take into account the various semi independent countries such as Isle of Man, Gilbraltar or Jersey, Guernsey and the other Channel Islands there is no level beneath country

    changes to countries are relatively rare (Southern Sudan was the most recent, the break up of what was Jugoslavia in 1991 the most significant in recent times) but changes to other elements can happen at anytime. in the UK the policiticians have been tinkering with the local government admin areas for the last 40 years, the most recent were 18 months ago
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Single table is a shortcut.
    Shortcuts invariably cost more in the long run.
    Three entities suggests three tables, or a single self-referential table (adjacentcy model or nested set).
    Plus, composite foreign keys play havoc with cascading updates/deletes.
    yes, we know all that -- you are just repeating routine dba dogma that's already been mentioned

    next thing you're going to do is advocate GUIDs so that "USA" can be related to "North America" in a permanent relationship which is immune to update changes when either one of those entities decides to change its name

    puh-lease

    i repeat, this case does not warrant the full-blown solution

    it's like getting into your ferrari to take the garbage to the curb -- it works, but it's inappropriate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I guess that I've been bitten a lot more times by under-engineering a solution than I have by over-engineering one. I've also automated a lot of the code, forms, etc. that I use to handle the "over-engineered" case automagically so it is actually LESS work for me than the compromise solution.

    I guess if you're starting from nothing, a compromise would come up quicker and it almost guarantees you more work down the road. I can see why it is tempting if you don't already have the framework that automagically supports the "over-engineered" solution available.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    because there is no single model that fits all such geographical data out in the world I don't see how a single table per level is going to work.
    the original problem here went down from major geographical region to subregion to country but no deeper

    states, counties, departments, and prefectures are irrelevant

    as you said, "there is no level beneath country"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Plus, composite foreign keys play havoc with cascading updates/deletes.
    yeah, for those easily-anticipated deletions where, you know, we remove an entire region from the planet like the middle east
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by r937 View Post
    the original problem here went down from major geographical region to subregion to country but no deeper

    states, counties, departments, and prefectures are irrelevant

    as you said, "there is no level beneath country"
    for now
    I'd rather be riding on the Tiger 800 or the Norton

Tags for this Thread

Posting Permissions

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