Jump to content
Xtreme .Net Talk

Recommended Posts

Posted

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

  • *Experts*
Posted

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

"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
Posted

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

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.

Posted

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?

  • *Experts*
Posted

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

"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
Posted

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

____________________________________________

http://www.pophamcafe.com

I am starting a developers section, more tutorials than anything.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...