Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2006
    Posts
    43

    Unanswered: relationship question

    I am developing asp.net using c# and SQL 2000 as backend database. I would like to know what is the best practice for class relationship implementation. Do I store the class in a Business Object table and the class relationship in a separate Relation Object table? What is the pros and cons? Or what is other better solution? I am in stage of design the object oriented database. Please advise and help me. Thanks.

  2. #2
    Join Date
    Nov 2006
    Posts
    43

    relationship implementation

    I am developing asp.net using c# and SQL 2000 as backend database. I would like to know what is the best practice for class relationship implementation. Do I store the class in a Business Object table and the class relationship in a separate Relation Object table? What is the pros and cons? Or what is other better solution? I am in stage of design the object oriented database. Please advise and help me. Thanks.

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    If you use declared referential integrity, the relationship of your tables is stored in the database.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2006
    Posts
    43
    May be I should give an example. Say I have a product class and I use product table to store the attribute and another class supplier and I use supplier table. Now I want to store the relationship between product and supplier. Say the relationship is product is supplied by many suppliers and supplier supplies many products. What is the best practice to implement this relationships. Do I use another separate table to store these relationships or other better implementation? Please advise me. Thanks.

  5. #5
    Join Date
    Nov 2006
    Posts
    43
    May be I should give an example. Say I have a product class and I use product table to store the attribute and another class supplier and I use supplier table. Now I want to store the relationship between product and supplier. Say the relationship is product is supplied by many suppliers and supplier supplies many products. What is the best practice to implement this relationships. Do I use another separate table to store these relationships or other better implementation? Please advise me. Thanks.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is called a many to many relationship. To model these in a relational database you use a third table and relate the two other tables to this, both using a one to many relationship. The third table's primary key is a composite of the primary keys of the other two tables.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Tables are the physical implementation of an Entity from a model. Entities in the model represent collections of things that exist in the real world. An entity can represent products (something that exists in a physical sense) or an entity can represent relationships between suppliers and products (something that exists in a logical sense).

    Based on what you've described, I see three entities. One entity for suppliers, one for products, and one for the relationship between suppliers and products. By making the relationship an entity, it makes it simple to handle the many-to-many relationship between suppliers and products.

    -PatP

  8. #8
    Join Date
    Nov 2006
    Posts
    43
    By making the relationship an entity, is it a good design and good practice?What are the pros and cons? Will this affect the performance? Please help. Thanks.

  9. #9
    Join Date
    Nov 2006
    Posts
    43
    Can I store the relationship in a table or in a object? Thanks.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hakeem777
    By making the relationship an entity, is it a good design and good practice?
    absolutely, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2006
    Posts
    43
    If I want to store many to many relationship, which one is better? Store it in a separate table for relationship only. Or store it in the same table as the object? In terms of performance, which one is better? Thanks.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using a relationship entity/table to express many-to-many relationships is both easy and efficient.

    How did you propose to express a many-to-many relationship with a single table?

    -PatP

  13. #13
    Join Date
    Nov 2006
    Posts
    43
    Just want to confirm that there is no way we can use single table or single object store the many to many relationship together with the object itself,right? What is the advantage of storing one to many relationship in a table over the referential integrity? Is it performance issue? Thanks.

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by hakeem777
    What is the advantage of storing one to many relationship in a table over the referential integrity?
    i'm not sure i understand this question

    the reason you store data in separate tables and link those tables together with foreign keys is to enable relational integrity

    relational or referential integrity is the purpose, and one-to-many relationships are the method whereby you achieve that purpose

    if you don't care about relational integrity, you mought as well store your stuff in flat files

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

  15. #15
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by hakeem777
    Just want to confirm that there is no way we can use single table or single object store the many to many relationship together with the object itself,right? What is the advantage of storing one to many relationship in a table over the referential integrity? Is it performance issue? Thanks.
    An Inclusion Dependency is a rule (constraint) that some set of values in one relation must be drawn from the set of values in another. The "other" set of values need not necessarily be a key of a relation. In ER modelling terms this would be referred to as a many-to-many relationship.

    Unfortunately, most SQL DBMSs don't have good support for the concept of a general inclusion dependency. SQL has a constraint called a FOREIGN KEY which enforces an inclusion dependency only where one side of the relationship is a super key (usually a candidate key but not necessarily so).

    Non-key forms of inclusion dependency are hard to implement and work with in SQL systems. For this reason it is usual to implement the many-to-many relationship only using a "joining table" - one which includes key columns from at least two other tables.

Posting Permissions

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