Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2006
    Posts
    3

    rough design for logically linked entities

    Hi all,

    Say supposed I have two Entities [Tables].

    1.] Users
    2.] Accounts

    Now these two entities can be associated in many ways

    1.] One to Many [e.g. One user can own multiple accounts].
    2.] Many to One [e.g. An account can be owned by multiple users. ]
    3.] Many to many [e.g. An user can access multiple accounts and An account can be accessed by multiple users.]

    Please suggest me appropirate DB design where in I can associate any two entities [Users and accounts ] by any type of association [1-1, 1-many, many-many]. Also i should have flexibility to attach and detach any association to/from any entity with minimum DB table alteration.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    3.] UserAccounts

    this table should contain two columns: FK to User and FK to Accounts

    the two columns together make up the composite PK for this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    3

    hi r937

    But this wud not take care of all the type of associations

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes it wud, er, i mean, yes it would
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    The Bitners Butcher is right

    UserAccount (please drop the plurals) is an associative table that allows User to have a many-to-many relationship with Account. If you create an index on UserAccount using the two key (look up the definition) columns concatenated, you will be able to ensure that you do not have duplicates (in your many-to-many relationship), otherwise you will have cancer, not a relationship.

    If, on the other hand, you want to allow all three types of relationships in the same table, then you have not finished your table and rship design yet. The whole idea is that any rship is one, and only one, of the following definitive (cardinality) types:
    - one-to-one
    - one-to-many (many-to-one is the same, just the other way around)
    - many-to-many
    When you have decided which one you need, then create your table. For one-to-many (one-to-one, many-to-one), you do not need a table to substantiate the rship, you need a ForeignKey in the associated table (that's what relational means); for a many-to-many rship, you need an additional table eg. UserAccount to substantiate it.
    Last edited by DerekA; 07-26-06 at 00:55.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Users is plural, Accounts is plural, please continue with this correct naming convention, and make UserAccounts plural too

    remember -- tables represent sets of tuples

    the only time a singular table name makes sense is if it contains only one row

    that said, this relationship table (UserAccounts) can and does easily support one-to-one, one-to-many, many-to-one, and many-to-many, depending on how you populate it

    many database designers will tell you that if there is any -- any -- chance that the relationship might ever be many-to-many, you would be wise to implement the relationship table right from the get go, even though you only populate it as one-to-one, one-to-many, or many-to-one to begin with
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    rudy is right about the use of an associative table to support any of the three possible scenarios

    as for the naming convention, I prefer the use of singular but I understand the argument for plurals. What is more important is to be consistent.

  8. #8
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Singular or Plural ?

    I will not respond to post #6, as it will get into a debate re whether singular or plural is correct. Whether singular or plural is "correct" is a matter of opinion. Obviously, I stand for IDEF1X (plus modernisations) and therefore for use of the singular for table names, amonst other things. It is the name of the table, not the description of its contents (whether one or more). The User table contains users; the car yard contains cars; the clothing store contains clothing. Definitely, be consistent.

    A table contains one set of tuples, not sets. The sets of tuples that can be derived by the column list or the where clause are subsets, there is only one physical set.

    BTW, do not use User/s or Account/s, as many db systems that implement ANSI SQL, or part thereof, or enhancements to, will implement Reserved Words, which cannot be used as table names, etc. Be a bit more specific and use (eg) [Application]User, WebAccount. Lazy designers often pluralise their table/column names just to get around the Reserved Word restriction.

    Everyone is a guru here, take what you like and leave the rest.

    Cheers
    Last edited by DerekA; 07-26-06 at 03:45.
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i will not respond to post #8 just as much as buddy did not respond to post #6

    i said tables are sets, i did not say a table are sets

    and buddy can call me lazy all he wants, it just shows his immaturity

    there's nothing in buddy's posts that i like much, and i'm not taking any of it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by certus
    What is more important is to be consistent.
    Amen to that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255

    Pluralise this ...

    Let's have a bit of fun with a real db (one that is not confidential). The only requirement is consistency. Pluralise these table names Family 0.8.pdf. Let's see, is that ...
    PersonsProgeny (many people one progeny. no)
    PeopleProgeny (many persons but whose (single ?) progeny)
    PersonsProgenies (correct by rule, but incorrect because a person has only one progeny)
    PersonProgenies (Jeez that's an awful lot of illegitimate children happening there)
    PeoplePeople (my favourite, concise, but quite meaningless)

    How about ...
    PersonEvents (one person many events ... but there are many persons)
    PersonsEvent (many persons one Event ... but there are many events)
    PersonsEvents (most reasonable by rule, but idiotic in English)

    Take a look at the SQL that you would need ...
    select Name from PersonsEvents where PersonId = xyz
    select PersonsId from PersonsCountries where CountriesCode = xyz
    select PersonId from PersonsCountries where CountryCode = xyz

    What about column names, should we pluralise them as well ? After all, the columns does have many values ... Take a look at the SQL that you would need ...
    select Names from Persons where CountriesCodes = "AU"
    select Names from PersonsEvents where PersonsIds = xyz

    I would much rather
    select Name from PersonEvent where PersonId = xyz
    Before making up your mind about singular or plural (or anything else for that matter), do try out a real world example, and the SQL required. Theory is great, but only after it is qualified by experience. Look for a bit of meaning, otherwise you will end up with nonsense.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    <sigh /> *plonk*
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Sep 2005
    Posts
    23
    Now these two entities can be associated in many ways

    1.] One to Many [e.g. One user can own multiple accounts].
    2.] Many to One [e.g. An account can be owned by multiple users. ]
    3.] Many to many [e.g. An user can access multiple accounts and An account can be accessed by multiple users.]

    Please suggest me appropirate DB design where in I can associate any two entities [Users and accounts ] by any type of association [1-1, 1-many, many-many]. Also i should have flexibility to attach and detach any association to/from any entity with minimum DB table alteration.
    In fact there is only one many-to-many relationship, which has to be split in two relationships between the tables you already have and a new one which will at least contain the keys of both as FK.

Posting Permissions

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