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