Re: [ubl-dev] UBL and a functional SQL schema

[David Lyon <david.lyon@computergrid.net>]

Hi Alex,

On Wednesday 23 February 2005 10:27 pm, alex black wrote:
>
> Of course, I can design my own, but no point doing it if someone else
> has gone to the trouble. Also - in thinking about implementations
> throughout the world - that is the natural question of any experienced
> pro who is building a system for a client: where's the SQL file
> (schema) as a starting point so I can store this stuff in a set of
> tables? XML Databases aren't production ready, and what limited looking
> I've done at XQuery shows it to be.. good but not really "there". Also
> if I'm doing anything else that related to the UBL docs it's nice to
> have a DB :)

I totally agree. What we do is store the xml/ubl document data in
a memo field (Trader_Documents.XML_Text), while hosting a lot of important 
fields neccessary for searching and the like, outside of that field.

Important queries eg "select sum(trader_documents.document_total)
where (trader_documents.Document_Type = "Invoice")..." can easily
be performed.

This results in a whole UBL based transaction system to be able
to exist in basically two tables (there are a few other supporting
ones) in a MySQL database.

# Host: GridServer44
# Database: ComputerGrid
# Table: 'Trader'
# 
CREATE TABLE `Trader` (
  `Trader_ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) NOT NULL default '',
  `Address_Line_1` varchar(30) default '',
  `Address_Line_2` varchar(30) default '',
  `Suburb_Town` varchar(50) default '',
  `State_Region` varchar(30) default '',
  `Zip_PostCode` varchar(10) default '',
  `Country_Code` char(2) default '',
  `Grid_Address` varchar(40) default '',
  `Lattitude` decimal(10,0) default '0',
  `Longitude` decimal(10,0) default '0',
  `Telephone_1` varchar(20) default '',
  `Telephone_2` varchar(20) default '',
  `Email` varchar(30) default '',
  `Trading_Status_Code` varchar(16) default '',
  `Trading_Relationship` varchar(16) default '',
  `AR_Account_Code` varchar(20) default '',
  `AP_Account_Code` varchar(20) default '',
  `Created` datetime default '0000-00-00 00:00:00',
  `Last_Accessed` datetime default '0000-00-00 00:00:00',
  `Balance_CP` decimal(10,0) default '0',
  `Balance_30` decimal(10,0) default '0',
  `Balance_60` decimal(10,0) default '0',
  `Balance_90` decimal(10,0) default '0',
  PRIMARY KEY  (`Trader_ID`)
) TYPE=MyISAM COMMENT='Company Master File'; 

# Host: GridServer44
# Database: ComputerGrid
# Table: 'Trader_Documents'
# 
CREATE TABLE `Trader_Documents` (
  `Document_ID` int(11) NOT NULL auto_increment,
  `Owned_By` int(11) NOT NULL default '0',
  `Shared_With` int(11) NOT NULL default '0',
  `System_Name` varchar(20) NOT NULL default 'STANDARD',
  `Document_Name` varchar(20) NOT NULL default '',
  `Document_Reference` varchar(50) NOT NULL default '',
  `Document_Date` datetime default '0000-00-00 00:00:00',
  `Document_Total` decimal(10,2) default '0.00',
  `Document_Tax` decimal(10,2) default '0.00',
  Status_Code` varchar(20) default '',
  `Remote_Status_Code` varchar(20) default '',
  `XML_Text` longtext,
  `Document_Options` mediumtext,
  `Update_Flag` char(1) NOT NULL default '+',
  `Msg_ID` varchar(30) default '',
  PRIMARY KEY  (`Document_ID`),
  UNIQUE KEY `byUpdateStatus` 
(`Owned_By`,`Shared_With`,`Update_Flag`,`Document_ID`),
  UNIQUE KEY `byTrader` 
(`Owned_By`,`Shared_With`,`Local_Status_Code`,`Document_Name`,`Document_Reference`,`Document_ID`)
) TYPE=MyISAM COMMENT='Company Document Master File'; 

Best Regards

David

-- 
Computergrid : The ones with the most connections win.