ModifiedPrePaidApplicationDatabaseConfiguration

Database Configuration / Layout

tabledescription
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

Image


Database Layout Dia File

Table cardtype
fieldnamefieldtypereferencesdescription
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.

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


Table card
fieldnamefieldtypereferencesdescription
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.

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


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


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


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


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


Table tariff
fieldnamefieldtypereferencesdescription
tariffidinteger primary key
tariffnametext Tariff name


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


Table provider
fieldnamefieldtypereferencesdescription
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
fieldnamefieldtypereferencesdescription
providerdestinationidinteger primary key
countryprefixidintegercountryprefix:countryprefixidReferences the prefix
provideridintegerprovider:provideridReference to the provider


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


Table call
fieldnamefieldtypereferencesdescription
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


Database Configuration / Layout

tabledescription
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

Image


Database Layout Dia File

Table cardtype
fieldnamefieldtypereferencesdescription
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.

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


Table card
fieldnamefieldtypereferencesdescription
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.

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


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


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


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


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


Table tariff
fieldnamefieldtypereferencesdescription
tariffidinteger primary key
tariffnametext Tariff name


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


Table provider
fieldnamefieldtypereferencesdescription
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
fieldnamefieldtypereferencesdescription
providerdestinationidinteger primary key
countryprefixidintegercountryprefix:countryprefixidReferences the prefix
provideridintegerprovider:provideridReference to the provider


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


Table call
fieldnamefieldtypereferencesdescription
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+