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.

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

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

yes it wud, er, i mean, yes it would

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.

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 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.

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

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

Amen to that.

pootle flump ur codings are working excelent.

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

<sigh /> *plonk*

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.