SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Storing Personal and Business Accounts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MevaD
Starting Member

32 Posts

Posted - 09/03/2013 :  21:14:09  Show Profile  Reply with Quote
Sorry if this is in the wrong category...

I need to store business and residential accounts in my databse (SQL 2012). I have tried a number of designs but I'm just not sure if I'm missing something. Current design looks like this:

Person 1 -- N PersonAccount N -- 1 Account 1 -- N Address

Where the Account table has a field called AccountType ('B' or 'R')

Some of the business rules are as follows:
1. A person can belong to many accounts.
2. An account can have many persons.
3. An account can have many addresses.
4. An address can belong to only 1 account.

If the above design looks OK, then what is the best query to select all persons that have the same account. And how to select all addresses on an account. ** Can this be done if I only have the personID to start?

Thanks.

P.S. This is not a homework assignment.

lazerath
Constraint Violating Yak Guru

USA
319 Posts

Posted - 09/05/2013 :  11:41:15  Show Profile  Reply with Quote
This looks very suspiciously like a homework assignment. What have you tried so far? What are your ideas on how to accomplish this?
Go to Top of Page

MevaD
Starting Member

32 Posts

Posted - 09/05/2013 :  11:53:32  Show Profile  Reply with Quote
No, as I said, this is NOT a homework assignent. I was just asking for input/feedback on my design. Thanks anyway.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

938 Posts

Posted - 09/06/2013 :  01:35:14  Show Profile  Reply with Quote
Without any DDL it's difficult to judge, let alone write queries for you. How about you start by posting your table definitions and some sample input/output?
Also, the account type is irrelevant according to your question but I assume you provided it for a reason. What is it?

To start with, I'd guess you want something like this
select personID from PersonAccount where accountid=?
gives you all persons with same account
select * from address where addressAccountID=?
gives you all address from account.

but it's impossible to know without more details.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000