Database Configuration / Layout

callStores the information about all calls (starttime/endtime/device/...)
cidCaller identification numbers get stored here for use with card
cardtypefor the different card types
cardInformation about calling cards which are in use at the moment
countryFull countryname for a countrycode
countryprefixPrefix for all countries + subcodes (FIX/MOB)
providerThe provider which can be used for outgoing calls (+which technology)
providerdestinationTells the system which provider can route calls to which destination
providerrateThe provider / destinations rates
resellerRegistered calling card resellers
orderStores information about new card orders
saleStores information about a calling card sale to a customer
tariffYou can have more than one defined tariff for your customers
tariffrateThe rates for each tariff per destination

Database Layout

Database Layout Dia File

Table cardtype
cardtypeidintegerprimary keyCardtype ID
valueinteger Start Credit
descriptiontext Card description
activeboolean Indicates if this Cardtype is sellable
tariffidintegertariff:tariffidReferences the tariff for this Cardtype
lifetimeinteger The lifetime of this card in days
frequencyinteger how often to pulse in seconds (for billing)
startpulseinteger how much to bill for the incoming call in seconds

Table cid
The first entry in this table is a dummy entry with the cidid 1, resselerid 1 and without a cid - this is necassary so that the foreign key at the table card can reference to this entry if the card is not associated with a real cid.

cididinteger Caller Identification ID
cidinteger Caller Identification
reselleridintegerreseller:reselleridReferences the reseller who has entered this cid
createtimedatetime When was it created

Table card
cardidtext primary key (random(16) digits - unique
cididintegercid:cididReferences the cid
payedboolean indicates if the card is already payed by the reseller
cardtypeidintegercardtype:cardtypeidreferences the cardtype
reselleridintegerreseller:reselleridreferences the reseller which has bought the card
creditinteger the actual available credit in cent
activatedboolean if the card is activated by the reseller
deactivateddatetime when was the card deactivated
expiresdatetime when will the card expire (NULL = never)

Table reseller
The first entry (resellerid=1) is your own company - is necassary for the foreign key constraint at the table card.

reselleridinteger primary key
usernametext(20) username for the reseller platform
userpasstext(20) password for the reseller platform

Table sale
saleidinteger primary key
cardidtextcard:cardidReference to the sold card
saletimedatetime When was the card sold

Table order
orderidinteger primary key
cardidtextcard:cardidReferences the card which was ordered
ordertimedatetime When was the order placed

Table country
countrycodetext International countrycode(primary key)
countrynametext International countryname

Table countryprefix
countryprefixidinteger primary key
prefixtext The Prefix (e.g. 43 for Austria)
countrycodetextcountry:countrycodeThe international countrycode
subcodetext Subcode (FIX = Fixed Line, MOB = Mobile)

Table tariff
tariffidinteger primary key
tariffnametext Tariff name

Table tariffrate
tariffrateidinteger primary key
tariffidintegertariff:tariffidTo which tariff belongs this special tariff
countryprefixidintegercountryprefix:countryprefixidReferences the Prefix
ratereal The costs in cent per minute

Table provider
provideridinteger primary key
carriercodetext The carrier code
providertechtext Which technology should be used to connect to the provider
provideriptext The ip if it is a voip connection
providernametext The provider name

Table providerdestination
providerdestinationidinteger primary key
countryprefixidintegercountryprefix:countryprefixidReferences the prefix
provideridintegerprovider:provideridReference to the provider

Table providerrate
providerrateidinteger primary key
provideridintegerprovider:provideridReferences the provider
countryprefixidintegercountryprefix:countryprefixidReferences the prefix
ratereal The costs in cent per minute

Table call
callidinteger primary key
countryprefixidintegercountryprefix:countryprefixidReferences the prefix
provideridintegerprovider:provideridReferences the provider
cardidintegercard:cardidReferences the card which was used for the call
tariffidintegertariff:tariffidReferences the tariff used for billing
channeltext the channel name
uniqueidtext a unique call id
starttimedatetime the call start time
stoptimedatetime the call stop time
sessiontimeinteger the complete session time in seconds
pulsetimeinteger the pulsed time in seconds according to the timing
callednumbertext the destination number
startdelayinteger time between the call start and the real call start in seconds
stopdelayinteger time between the call end and the real call end in seconds
terminatecausetext how was the call terminated
sessionbillinteger the costs for this call in cents

Created by: wuwu, Last modification: Thu 03 of Jun, 2004 (10:36 UTC)
Please update this page with new information, just login and click on the "Edit" or "Discussion" tab. Get a free login here: Register Thanks! - Find us on Google+