Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778

    Entity Instances

    Hello,

    I was having a discussion with someone at work about how to represent certain entities. I will explain through examples:

    From reading an earlier post in this form, I can relate our discussion to points raised in the previous post.

    Region (region_text)

    Country(county_name, etc)

    A country can have many regions, and a region (on it's own) can have many countries. This was the underlying theme of the discussion. In many cases this would be modeled as:

    Country (country_name, etc)

    Country_Regions (region_text, country_id)
    UK, South
    UK, NorthEast
    ...
    USA, South
    USA, North
    USA, WestCoast

    The alternative suggested was why not, create two tables; Regions and Countrys and then join the corresponding Region to Country. An obvious reason not to, is that a region logically cannot exist without a country. This then lead to the question "Well, if there are 100+ countries that all have North, South, East and West" (For example), then would this not save data storage.

    Then, how is a town related to a country and region? Through, aggregation simply. But I don't want to sidetrack there, as the underlying theme is the creation of Region as a single entity. I have a couple of other examples which were discussed that may illustrate the point I am trying to make.

    Books and Book Titles

    Book (ISBN, book_title [FK])
    Book_titles ([book_title])

    I wouldn't create the dependent <=> employee relationship in this manner.

    Employee (emp_id, first_name, etc)
    Dependent(dep_name)

    Emp_dependent(emp_id, dep_name)

    But, If I did, I don't see what problems would be caused. Of course you cannot update the dependent relation now.

    I then look at standard lookup tables:

    Employee_Status ([status_id], [status_text])

    UPDATE employee_Status
    SET status_Text = "newText"
    WHERE id = 1

    I believe would not be a problem, providing this change was allowed logically.

    But I am having doubts about applying this same lookup style table to Forms / Form_Questions.

    Form1, "What is your name"
    ...
    Form2, "What is your name"
    ..
    Form3, "What is your name"

    Why not put "What is your name", in a questions lookup table:

    Questions(Question_ID, Question_text)
    Forms (form_id,Form_title, industry, etc)
    Form_Questions(form_id, question_id)

    UPDATE Questions
    SET question_text = "newQuestionText"
    WHERE id = 1

    This, I believe would cause problems.

    If I update Questions.Question_text ("What is your name"), to "What is your full name", this will obviously reflect across all forms. But is this acceptable, because I'm not actually updating a question, instead as question_text is Unique, I am technically, creating a new instance and deleting the old.

    I am concerned that If I adopt this approach then many non-intentional errors may occur, Ie, forms 16 - 20 did not want that text to appear. So why not just create a new question instance and change the many to many relationship of forms 1-15 to reflect this. I am not sure, does this seem logical?

    This approach could then be used for forum message titles, and book titles.


    Thank you for reading.
    Last edited by r123456; 02-27-05 at 07:28.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    After thinking about it some more,

    Dependent([dep_name]) is said not to be unique, because a dependent can have more than a single name. This is based on the relationship.

    So Is a question unique, in this same manner. Some might say, Many forms can have the same question_text, thus question is not unique by question_text. Surely though, this depends on the relationship. The person I was having a discussion said, why not have a predefined list of questions. But that would suggest that a question would be unique on question_text.

    I think I'm missing something obvious here.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    having questions as a separate entity is not wrong

    it all depends on how you identify them

    you are on the right track when you do this without resorting to surrogate keys

    okay, so is "what is your name" actually the same question on all forms?

    the answer is yes

    i also agree with your other thought about how an update to this question is in effect a delete/insert

    me personally i would use a question table, and a many-to-many relationship table to relate questions to forms

    i probably wouldn't use a region table, though

    instead, i would simply have region as a one-to-many child of country

    there's nothing wrong, in my mind, with having "East" as the name of many different regions

    because they actually have different identities (despite having the same name)

    in this case, region is a dependent entity, and part of the primary key is the foreign key to country

    the only way i'd use a many-to-many country-region design would be if it were necessary to include regions which span countries, like "the balkans" or "the caribbean"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Thanks for the reply, Rudy.

    I guess some people would identify a question as (Question_text, Form). This would then be a Question. This is what had me confused, I believe. As you said, I think it depends on how the individual chooses to identify them.

    As with what you said about regions. It could be possible to have regions as a single table, and state that a region is identified by its name. In this case, the region North can be related to many countries and so forth. However, I think this is the common point. If region is modeled as a single entity and related to countries through many to many, it no longer becomes a region. Instead, it becomes some meaningless text, in my opinion.

    But like you said, given that in reality a region is logically a child entity of country, there is no reason to model it as a many to many relationship.

    Thanks for taking the time to reply, as your post has helped me understand. I really appreciate it.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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