SalesDcid PHYSICAL Table Structure

This page contains technical detail about the physical database structure. It is not required for normal day to day operation of the Point Of Sale.

Goto: Documentation Home

This table holds the links between individual derived customers and sales. It is typically required for creating private reports based on derived customers

See also: CustomerDcid table

Columns

FieldName	Datatype	Meta Info	Gnap	Description
dcid	long	Primary Key.	100/E	A unique temporary internal id assigned to this derived customer. Refer to CustomerDcid table for more details
sid	long		101/s	Sale Id this record refers too

Indexing

A unique primary key should exist on the sid field.

An optional non-unique index can exist on the dcid field.

This table is frequently completely emptied and repopulated. Adding excessive indexing can slow this repopulate process on some databases

Remarks

This table is primary useful for grouping isolated sales together. It will typically be used solely as a linking with with SQL similar to the following

	select .... from sales s, salesdcid sd where s.sid=sd.sid .....

Or to generate the raw spend and other overview statistics for each derived customer (example only, not for production use)

	select cust.name,count(*),sum(s.saletotal),avg(s.saletotal),min(s.completeddt),max(s.completeddt)
		from sales s, salesdcid sd, customersdcid cust
		where 
			s.sid=sd.sid
			and sd.dcid = cust.dcid
			and s.phase = 1
		group by sd.dcid
		order by 3 desc

Design note. A seperate table was used to hold this information rather than adding a column to the sales table as this information is frequently cleared and refreshed while the sales table tends to hold static and historical information