ModifiedPrePaidApplicationDatabaseConfiguration

 

table	description
call	Stores the information about all calls (starttime/endtime/device/…)
cid	Caller identification numbers get stored here for use with card
cardtype	for the different card types
card	Information about calling cards which are in use at the moment
country	Full countryname for a countrycode
countryprefix	Prefix for all countries + subcodes (FIX/MOB)
provider	The provider which can be used for outgoing calls (+which technology)
providerdestination	Tells the system which provider can route calls to which destination
providerrate	The provider / destinations rates
reseller	Registered calling card resellers
order	Stores information about new card orders
sale	Stores information about a calling card sale to a customer
tariff	You can have more than one defined tariff for your customers
tariffrate	The rates for each tariff per destination

Database Layout
Database Layout Dia File

Table cardtype

fieldname	fieldtype	references	description
cardtypeid	integer	primary key	Cardtype ID
value	integer		Start Credit
description	text		Card description
active	boolean		Indicates if this Cardtype is sellable
tariffid	integer	tariff:tariffid	References the tariff for this Cardtype
lifetime	integer		The lifetime of this card in days
frequency	integer		how often to pulse in seconds (for billing)
startpulse	integer		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.

fieldname	fieldtype	references	description
cidid	integer		Caller Identification ID
cid	integer		Caller Identification
resellerid	integer	reseller:resellerid	References the reseller who has entered this cid
createtime	datetime		When was it created

Table card

fieldname	fieldtype	references	description
cardid	text		primary key (random(16) digits – unique
cidid	integer	cid:cidid	References the cid
payed	boolean		indicates if the card is already payed by the reseller
cardtypeid	integer	cardtype:cardtypeid	references the cardtype
resellerid	integer	reseller:resellerid	references the reseller which has bought the card
credit	integer		the actual available credit in cent
activated	boolean		if the card is activated by the reseller
deactivated	datetime		when was the card deactivated
expires	datetime		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.

fieldname	fieldtype	references	description
resellerid	integer		primary key
username	text(20)		username for the reseller platform
userpass	text(20)		password for the reseller platform

Table sale

fieldname	fieldtype	references	description
saleid	integer		primary key
cardid	text	card:cardid	Reference to the sold card
saletime	datetime		When was the card sold

Table order

fieldname	fieldtype	references	description
orderid	integer		primary key
cardid	text	card:cardid	References the card which was ordered
ordertime	datetime		When was the order placed

Table country

fieldname	fieldtype	references	description
countrycode	text		International countrycode(primary key)
countryname	text		International countryname

Table countryprefix

fieldname	fieldtype	references	description
countryprefixid	integer		primary key
prefix	text		The Prefix (e.g. 43 for Austria)
countrycode	text	country:countrycode	The international countrycode
subcode	text		Subcode (FIX = Fixed Line, MOB = Mobile)

Table tariff

fieldname	fieldtype	references	description
tariffid	integer		primary key
tariffname	text		Tariff name

Table tariffrate

fieldname	fieldtype	references	description
tariffrateid	integer		primary key
tariffid	integer	tariff:tariffid	To which tariff belongs this special tariff
countryprefixid	integer	countryprefix:countryprefixid	References the Prefix
rate	real		The costs in cent per minute

Table provider

fieldname	fieldtype	references	description
providerid	integer		primary key
carriercode	text		The carrier code
providertech	text		Which technology should be used to connect to the provider
providerip	text		The ip if it is a voip connection
providername	text		The provider name

Table providerdestination

fieldname	fieldtype	references	description
providerdestinationid	integer		primary key
countryprefixid	integer	countryprefix:countryprefixid	References the prefix
providerid	integer	provider:providerid	Reference to the provider

Table providerrate

fieldname	fieldtype	references	description
providerrateid	integer		primary key
providerid	integer	provider:providerid	References the provider
countryprefixid	integer	countryprefix:countryprefixid	References the prefix
rate	real		The costs in cent per minute

Table call

fieldname	fieldtype	references	description
callid	integer		primary key
countryprefixid	integer	countryprefix:countryprefixid	References the prefix
providerid	integer	provider:providerid	References the provider
cardid	integer	card:cardid	References the card which was used for the call
tariffid	integer	tariff:tariffid	References the tariff used for billing
channel	text		the channel name
uniqueid	text		a unique call id
starttime	datetime		the call start time
stoptime	datetime		the call stop time
sessiontime	integer		the complete session time in seconds
pulsetime	integer		the pulsed time in seconds according to the timing
callednumber	text		the destination number
startdelay	integer		time between the call start and the real call start in seconds
stopdelay	integer		time between the call end and the real call end in seconds
terminatecause	text		how was the call terminated
sessionbill	integer		the costs for this call in cents