With the use of schemas (instead of just dbo as the schema) now, there is a
design question I have... Say you have three entities that have tables
representing them such as People, Accounts and Businesses... how would you
represent this schema wise? Our business logic says users don't necessarily
have access to all three... so we figured that people related information is
under a "People" schema... like PersonalInformation table... etc.., and
Account information is its own Accounts schema... so we have
AccountInformation, AccountOrders (since orders are from accounts only)..
does this make sense from a schema point of view? each of the three have
different security setups... so it seemed to make sense from that point of
view...

now the real question... people accounts and businesses can all have
addresses... how would you set this up? would you make one address table and
make a one to many table to link them (as a person could have more then one
address, as also an account...) or would you make address tables at each
schema level ... so People.Addresses, Accounts.Addresses... kind of like how
the AdventureWorks database does for the different departmental schemas...

what are the pro's opinions? thanks!

I am squarely in the camp that you shouldn't use something just because it
is available. Things are MUCH simpler if you use dbo as the schema for all
objects. You may want/need to do otherwise in certain situations, but I try
to avoid non-dbo when at all possible.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Smokey Grindel" <nospam (AT) nospam (DOT) com> wrote