Commonly Used Database Tables

Learn about the commonly used database tables and their functions.

Below are the commonly used databases and their description:

Table Name	Description
Customer	Contains information related to a Customer, including: Customer Name - This could be a Company Name. CustID - This is also the Record ID. CustNo - This is the Customer Account Number. Note: The phrases Customer Number and Account Number are often used interchangeably. They all refer to the CustNo.
CustomerAdditionalInfo	This links this Customer to other valuable information such as their DUNS ID, their Billing Date, Billing Type, and other account state information.
Premise	This is a representation of a physical property which can be a single home, a building, an entire apartment complex, a single unit in an apartment complex. Note: A Premise is associated with a Customer, and a Customer can have 0-n premises.
Address	This provides physical mailing addresses for several tables in the system. Premise, Customer, LDC, and other tables store address information in this table. An Address is mostly used to get the mailing address for a Premise.
Meter	It measures the consumption of gas or electricity on a premise. A Meter has an `ESIID` and a `Meter Number` (MeterNo). A meter is linked to a Customer, a premise, and an address. Note: A premise can have 1-n meters.
Consumption	This refers to gas or electricity usages as measured by a meter. Associated with a Meter and an Invoice (if the consumption has been billed to the Customer yet). The consumption record tracks when the consumption was read, energy usage either as a begin-end value or as a usage value for the term, and the begin and end date of the consumption.
ConsumptionDetail	This contains data read from meter registers. The new electronic meters have a small bit of memory called a Register, which can be programmed to store or accumulate certain kind of data. When meter data comes with register data values, those values are stored here. If a meter has more than one register, each register value is written as a record in this table. Thus, there may be many ConsumptionDetail records for each Consumption record.
Invoice	The Invoice Table stores information related to an individual invoice. An invoice is linked to a Customer. The complete amount of the invoice is stored in this record. Each line item on the invoice is stored in InvoiceDetail.
InvoiceDetail	An Invoice contains line items. InvoiceDetail contains a record for each line item in an Invoice. Each line item is linked to a meter, premise, county, and state.
Rate	Customers are charged for electricity and gas that they used. The price they pay depends on the rate plan they have signed up for, which is created by the reseller. The Rate Table keeps track of the different rate plans, their effective and expiration dates, and any information related to identifying the rate plan itself. It does not contain the actual rate charged for the consumption.
RateDetail	The RateDetail table contains specific information about the rate plan, the charged rate, the rate for a particular period of time, profit margins, etc. The structure permits the rate to change over time, thus multiple records can detail what the current rate is, when it changes, and what it changes to.
Payment	The records in the Payment table keeps track of payments from Customers. When the payment is received, it is noted in a bank transaction which generates an 820 message to the market stating the Customer paid their bill. A payment is linked to a LockBox by `LockBoxID`. The record contains the check amount, the date the check was posted and when it was paid, etc.
PaymentDetail	The PaymentDetail contains specific payment information about a payment such as the payment amount, and the date the payment was posted. It is linked to a Customer by `CustID` and `CustNo` and to an Invoice by `InvoiceID`.
CustomerTransactionRequest (CTR)	The imported data coming from the Global Service EDI documents passing through the <ClientN>Market database and then to the <ClientN> database is sent to the CustomerTransactionRequest (CTR) table. The CTR table links the Market Transaction to the Customer by `CustID`, to the Premise by `PremID` and `PremNo` which is the same as ESIID. Each record in CTR contains the following: Transaction Type (810, 820, 824) Action Code (A, B, CF, 10, etc.) Transaction date Direction (inbound or outbound). Transactions from the market have a `TransactionNumber`, which is stored in CTR. Each CTR record has a `RequestID`, which links it back to the original raw data in the `BillingAdmin` database and to the `ChangeRequest` table.
ChangeRequest
BillingAdmin..EventActionQueue	Used by the Event Engine to perform operations on data as requested by the Supplier application/CSR.
BillingAdmin..EventingQueue	Used by the Event Engine to perform operations on data as requested by the Supplier application/CSR.
<ClientN>Market..<tables>	This stores incoming messages from the market and populated by the Global Service.