OnTheAnvil Posted November 12, 2004 Posted November 12, 2004 Does anyone know the best way to link a single record to multiple records in another table? For instance I have the following tables: Table: CUSTOMER ----------------- Field: CustomerID Field: CustomerName Table: ACCOUNT ---------------- Field: AccountID Field: CustomerID Field: AccountName Currently I have multiple accounts per customer but not multiple customers per account. What I would like to do is set up a BILL table that would allow me to link a CUSTOMER to one or more of their ACCOUNTS. Each customer could have infinitely many accounts. What I was thinking I could do is create a BILL table like the one below: Table: BILL ----------- Field: CustomerID Field: AccountID But this means that a bill would have muliple rows because it would need one row for each account that that bill is linked to. Does anyone know a better way of doing this? Thanks Quote
*Experts* Nerseus Posted November 12, 2004 *Experts* Posted November 12, 2004 If you want multiple Accounts per Customer, the first two tables give that to you - it's the CustomerID in the Account table that allow that. Make sure you don't have a unique key/constraint on the Account table's CustomerID. That gives you a 1 to 0-to-many join. You would introduce a Bill table with CustomerID and AccountID if you wanted a many to many join, which you said you don't want. -ner Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
Joe Mamma Posted November 12, 2004 Posted November 12, 2004 you're on the right track. customer 1--<N Account 1--<N Invoice 1--<N InvoiceLineItem Cust ------ CustID PK Account ------ CustID References Cust(CustID) AccountID PK Invoice ------ AccountID References Account(AccountID) InvoiceID PK DateOfInvoice InvoiceLineItem ------ InvoiceID References Invoice(InvoiceID) ItemID References Item(ItemID) UnitPrice Quantity PrimaryKey (InvoiceID, ItemID) Item ------ ItemID Pk Description MSRP WholeSalePrice Quote Joe Mamma Amendment 4: The right of the people to be secure in their persons, houses, papers, and effects, against unreasonable searches and seizures, shall not be violated, and no warrants shall issue, but upon probable cause, supported by oath or affirmation, and particularly describing the place to be searched, and the persons or things to be seized. Amendment 9: The enumeration in the Constitution, of certain rights, shall not be construed to deny or disparage others retained by the people.
OnTheAnvil Posted November 12, 2004 Author Posted November 12, 2004 Thanks for the reply but I don't think I worded my question correctly. Let me try again. I want to bill a customer but I need to be able to create a bill based off SOME of their accounts. For instance a customer might have the following accounts: Company Car Office Supplies Internet Service Movie Rentals If I wanted to bill them for their business expense I would create a bill linked to the following accounts: Company Car,Office Supplies,Internet Service but NOT their Movie Rentals. The movie rentals would be billed seperately. Does that make more sense? Quote
*Experts* Nerseus Posted November 12, 2004 *Experts* Posted November 12, 2004 It sounds like your tables are still fine - you just want a query that has a WHERE clause. Or, are you asking for something else? For example SELECT Customer.*, Account.* FROM Customer INNER JOIN Account ON Customer.CustomerID = Account.CustomerID WHERE Account.AccountName IN ('Company Car', 'Office Supplies', 'Internet Service', 'Movie Rentals') Not knowing what you want returned or in what format, I can't help more. But the above would return all customers and their matching accounts. If you need more help, you'll have to ask a more specific question. I was under the impression your original question was more how to create a Bill table (which you don't need based on your description). If you want one row per customer, then you'll have to fake a new table. For example, if you want one row to have a customer and 4 columns with the "Company Car", "Office Supplies", etc. info then you'll need more advanced code to "flatten" out the data from a row-based format (the Account table) into columns. You could do that with a temp table, or a number of sub-selects or other options. Depending on the size of the database/project, this is what a Data Warehouse is for. A warehouse allows you to flatten out some tables for speeding up reports where a normalized table structure doesn't work as well. -ner Quote "I want to stand as close to the edge as I can without going over. Out on the edge you see all the kinds of things you can't see from the center." - Kurt Vonnegut
cpopham Posted November 15, 2004 Posted November 15, 2004 They could also do it like the traitional customer, invoice, inv_line table scenario. You could have a customer tables as already set up. Then have the bill table as your described it. Then you could create a bill line table which would have a foriegn key of the primary key of the business table. You would then have a 1 to M relationship between the bill table and bill line table. Then you could put the bill together by whatever accounts were listed in the bill line table. Something like this: Customer Table: Cust_ID Cust_LName Bill Table: Bill_ID Bill_Date Account Table: Acnt_ID Cust_ID Acnt_Name Bill_Line Table: BL_Num Bill_ID Acnt_ID BL_Amt Hope this helps. Chester Quote ____________________________________________ http://www.pophamcafe.com I am starting a developers section, more tutorials than anything.
OnTheAnvil Posted November 15, 2004 Author Posted November 15, 2004 Great. I think you guys have answered my question. Thanks alot. Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.