[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]
Subject: Re: [ubl-dev] UBL and a functional SQL schema
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.
[Date Prev] | [Thread Prev] | [Thread Next] | [Date Next] -- [Date Index] | [Thread Index] | [List Home]