This database is offered as a guide for the semester's exercise and is similar in structure to some of the best real systems I've seen and worked in.  Lots of (100's of) columns and (dozens of) tables have been omitted for our fictitious Enterprises, which have the overriding requirement that their systems be complete by the end of the semester.   The database described here is the result of being deployed a dozen or more times in classes similar to yours and in my work over the years.   Neither the format in which the database is presented nor the column names supplied are appropriate for a database & data dictionary to meet any of the requirements for assignments. The presentation style and method for describing the columns is ugly and is chosen to encourage students to adopt a Data Dictionary style of their own and use it to describe their own database. Since it represents the internal data structures for an Enterprise it may be modified, enhanced, or replaced.  Students are welcome to use any data structure they can diagram and document as long as it will produce the standard psX12 documents and keep the Ledger Engine updated with Journal Vouchers that are properly formatted and net zero.  However, please be advised that the Instructor's ability to help with problems may be limited if other tables or data structures are used.   Green columns are not needed until ecommerce functions are added in project #3 and are offered only as a suggestion that would support one automatic fulfillment scheme. Depending on the needs of the particular enterprise, the sample columns may or may not be appropriate or sufficient to handle psX12 requirements.
	The descriptions used as headings in the columns of the table descriptions below are not intended to be used as column names in your tables.  Using the function as a guide, each student should name their tables and columns in a consistent & meaningful manner using as much or little abbreviation as desired and either incorporating the tables' names in the columns' names or not.
	Full General Ledger Account Ids are a three part code maintained by the Ledger Engine for each Enterprise that submits JVs.  They are in the format 1234-100-2000.   For Summer '03, the Activity Code is not required.   The first group of four digits is an Enterprise's Id, the group of next three digits ia an Activity Id, and the last group of four digits is a Ledger Detail Id.  Since each Enterprise Engine is accounting only for its activity it may leave out the first group for its local accounting and reporting. When required, a valid Activity id is placed on each Order and each Detail for an order is coded with an appropriate Ledger Detail id.  The Activity Id and the Ledger Detail id are used in journalizing and reporting the details of an Enterprise's activity.   If you'd like to add 'checks and balances'  expected in a 'real' enterprise then you're welcome to add this table and use it to accumulate debits and credits, although this service will be provided for your Enterprise by the Ledger Engine.
	The Enterprise Definition record is an asterisk delimited file, not in the database, containing a single record that defines the Enterprise for which an Enterprise Engine is journalizing activity.  It is stored in the same directory as the PowerBuilder Library (pbl) & Adaptive Server Anywhere (db) files.   The easiest way to put it there is to use the NotePad.  Make sure to put the file name in quotes as you save the enterprise.def record or it will be saved as enterprise.def.txt and this may further be confused if your Windows explorer is set to Hide Extensions for known file types. It is named Enterprise.def and is formatted as:

Activities form the second group of 3 digits in the General Ledger Account in the Ledger Engine, and the 1st group in most Enterprise Systems.  These are like: accounting, marketing, purchasing, selling, taxing, manufacturing, shipping, allocating, book sales, membership, &c, &c.  Enterprises can use three as a minimum: 100 - Startup; 200 - Sales & Service; 300 - Admin.  Have any others you need added to the Ledger Engine to support the reporting for your Enterprise. As each new Order is defined a valid Activity Id is assigned to the order.  This way, audit trail and other reports from the Details can be made for a selected Activity.   Id Text Integer 100 - 999 Text Describing Activity

Ledger Accounts  form the third group of 4 digits in the General Ledger Account.  These are the detail lines in the chart of accounts in the ledger engine for each fictitious Enterprise in our class project. See the Chart of Accounts page for the accounts that are provided in the Ledger Engine. We are using five types of accounts: assets, liabilities, equity/capital, income, and expense.  The type of account is identified by the first digit, 1 - 5, respectively. The appropriate Ledger Account Id is assigned as each Detail (see below) is added to an Order processed by the enterprise.  It is assigned with reference to the Ledger Code At Sales & Ledger Code At Purchase stored in the Goods & Services record on each Detail line.

Id	Text
Integer	Text Describing Account
1000 - 1999	Asset
2000 - 2999	Liability
3000 - 3999	Equity
4000 - 4999	Income
5000 - 5999	Expenses

Business Entities are the people and other organizations that your Enterprise relates to.  Each of the other Enterprises in our class project, plus the companies & individuals who are customers, suppliers, users, employees, &c related to each enterprise.  If it exists and is an person, company, or other entity that can issue or receive orders then it should be represented by a record in the Entities table.   If your Enterprise is mostly Cash oriented, as at a hot dog stand, then your Enterprise should have a Business Entity record setup for Cash Customer and its sales order can default to this Entity Id. A Business Entity with an Enterprise ID is one built by another student in class who has placed items in the Organization's Catalog and is ready for psX12 trade.   Every other Business Entity in the table should have the Enterprise Id as an Empty string or as Null data.

ID	Business Entity's Name	Extra Line of Description or Address	Mailing Address; Post Office (City, ST); Zip	Relationships with Enterprise, Privileges or authority provided if this is an on-line User	psX12 Enabled Enterprise ID	User's Login Id	User's  Password	Record's Source & ID from that source	Other Addresses: email,  Physical/ Shipping, vacation, Phone, fax,  pager#, location codes, &c
Auto incremented Integer	Char 45, a minimum of two columns: Last Name & First Name.  An organization's name is stored in the Last Name column with an empty or Null string stored in the First Name.   An Entity's name can be expanded to several discrete elements as needed for enterprise correspondence. (Salutation, prefix, first name, last name, suffix, title, etc.)	Char 45 field with optional text to further expedite delivery of mail to this Entity on Sales and Purchasing Details, mailing labels & other docs.	At least three columns here hold fields for  Address, City, State,  Zip Code.   CASS certifiable records need Zip + 4, Carrier Route, & Delivery Point.	A String indicating the entity's primary relationship to the Enterprise.  One way to handle this: Relationships are entered as text, words separated by spaces.  Samples would be: Manager, supplier, Salesperson, customer, employee. More than one relationship is possible, the primary relationship is listed first.  These are used to select entities as needed for various reports, the first of which is mailing labels for Customers.	A 4 digit char field used to identify other psX12-enabled Enterpises.  It is left Null or empty for Business Entities that are not Enterprises participating in the class project.   This should not be an integer since some Enterprise Ids start with leading zeros.	A char field used to hold the Login Id for those Entities who are Users of the application.  Make the Owner/Manager functions available to a user with Login Id of 'Manager'.  Make the Sales functions available for a Login Id of 'Sales'. Make Customer functions available for a Login Id of 'Customer'.   If this is a 'desktop' application make the password the same as the login id.  If it is web-based, make sure to supply the instructor with the ids.	A char field holding the Users' passwords.	When records are 'Taken On' from a file provided by some other Enterprise these columns can help  avoid creating Duplicate records when the file is presented again from the same source.	At least an email address is required for psX12 transactions. You'll also need street address, city, state, & zip code to support drop shipping via psX12 if you want to be most realistic.

 

Goods & Services are what that pass through, what are produced & consumed by, and what become assets of the enterprise.  Anything that can be bought and/or sold by the enterprise, including: stock/equity, goods, or services may be placed on the detail lines of the orders of our class project so they all must be represented by entries in this table.   The GS table also includes records for each Tender accepted or offered by the Enterprise (cash, ACH, check, credit card, barter) so that the Details of an Order can be Journalized with reference to this one table.  For the purposes of this course it is entirely acceptable to make your Enterprise operate on a 'cash only basis.  But, you may want to include accounts payable & receivable, loans, or other tenders to make your system more realistic.' This table is greatly simplified in that it has no 'distribution' functions such as 'Unit of Measure' (pallet, case, carton, package, each, + more than 100 others defined in ANSI X.12) and it has no other pesky values like size or color to handle.  Description should include the Unit of Measure in its text if it is not 'Each'.   For an Enterprise's price list and catalog entries only those Goods & Services with Activity of 200 - Sales, or other rule appropriate for the core activity of the Enterprise, should be selected.

Id	Class	Description of Good/Service	Cost when Purchased	Ledger Code when Purchased	Price when Sold	Ledger Code when Sold	Quantity On Hand	Primary Supplier Entity Id	Primary Supplier's PartNum & Price	Standards, Etc, &c....
Auto- incremented integer	One Char to represent: Goods, Services, Tender, Asset, Equity, eXpense, Rental, or others as your enterprise requires.	Char describing the good or service, includes Unit of measure: Hour, Each, Pound, Carton, as needed for your Enterprise.	Money, Cost at purchase, Null or zero if variable or used  to maintain established cost here.	Integer, Foreign Key, Ledger Code of Expense or other account to debit when used on Purchasing details.	Money,  Price when selling this GS, Null or zero if not fixed and determined on each detail.	Integer, Foreign Key, Ledger Code of Income or other account to credit when used on Sales details.	Money, Qty On Hand, updated from the as Details are processed. Should not be editable by users!! Should be updated by a process like the clicked script for cb_detailOK.	Integer, Foreign Key,  the Entity Id of the main Supplier of this GS who will receive psX12 PO for this GS.	Integer PartNum (is a GS Id#), Supplier's part number as required on the purchasing details when orders are placed with this supplier, available from REQUEST*CAT results.   Price should be Money.	In a 'real' enterprise, this may expand to some 100+ other fields important in the details of its operation.

 

Accounting Date (Not Required for Summer '03) records store the status of the Accounting Date used in an Order record.  Orders can only be processed when the status of the Accounting Date is Open.  Another valid status is Closed.  Each Enterprise is free to add other status codes to control its order entry process.   Ordinarily, an Accounting Date record's status should be Closed before a JV transaction is prepared and sent to the Ledger Engine.  The sum of all Details posted on Orders for an Accounting Date should net zero before the status can be changed to Closed. Day Status Date Open, Closed, or others as needed

Orders used here are two of the myriad types of orders processed by a 'real enterprise.'   For our purposes these two can suffice: Sales Order & Purchase Order.  You are welcome to use other types of orders as would be appropriate for your particular Enterprise.  For example, it would probably be good to use a special type of order to record the details of transfer of equity, or fixed assets, and tender for these orders. The order entry system for the Enterprise Engines should be flexible enough to record every detail of the start up these fictitious Enterprises as well as record all the Goods & Services Sold and Purchased as it operates.   When your Enterprise purchases goods from a supplier or services from an employee or its owner (no payroll taxes in our fictitious domain) it can buy these from your employees on a Purchase Order where the details record Debits and increase the QOH.  Tender on a Purchase Order needs to show a Credit to Cash, or another tender account like Accounts Payable. Items purchased as Fixed Assets should debit Asset accounts. When your Enterprise sells its goods or services they go on a Sales Order and the numbers on the Details effect a Credit to a Sales Account (4000 or 4010) and decrease the QOH.  For an Order to be closed at the end of a day all its detail lines must net zero.  To close an accounting date, all the orders for that date must net zero.  (Tenders = Goods&Services transferred).

Id	Order's Business Entity Id	Accounting Date for this Order	Order's Type	Debit/ Credit Indicator	Activity Id (Not for Spring '03)	Queued for psX12	Sent by psX12	Invoice received by psX12	Scheduling, Job Acc'tg,  Etc...
Auto- incremented Counter	Integer, Foreign Key, Id of the customer or supplier issuing or receiving the order	Date used for preparing accounting journals, assigned as Foreign key to Acc'tg Date: it must be present and in Open Status to be used on an Order.	Char, at least S or P: Sales or PO, or in a real enterprise: Work Order, Warehouse Order, Time Sheet, &c.	Integer, 1 on Purchase Orders, -1 on Sales Orders.  The sign on the Qty of Details is assigned with reference to this.	Integer, Foreign Key, ID of the activity reflected on the order: 100 Startup; 200 Sales & Service; 300 Admin.	DateTime when this order was queued for psX12 email.	DateTime when this order was sent via psX12 email.	For PO only, DateTime when an invoice was received via psX12 email.	This may expand to 100+ fields as needed in enterprise...

 

Details record the 'line items' on an Order that are printed or displayed on the screen.  Reporting for Sales and Purchases also happens from the Detail lines.  And, the Debits and Credits in Details are Journalized to make the vouchers posted to the General Ledger for each day's activity. In this model the Details hold all the data required for accounting. The Quantity column indicates debit or credit so that details net zero when they are grouped by Ledger Detail Id and the product of qty*each is used as the amount of debit or credit.  The sign on the quantity is determined by the Enterprise Engine as data input for each detail is edited. The order entry clerk should not have to be concerned with entering debits vs. credits.    On details recording Tender Quantity takes the sign of -1 * CurrentDrCr as the detail is edited. On a Sales Order, each Details' Quantity receives a negative sign (multiplied by the -1 in the InOut or Debit/Creit field above) indicating a Credit to a Sales Account.   The Ledger Detail Id is assigned with reference to the GS record for the GS Id nominated on the detail line.  If Qty is negative (Cr) after the above operation, then LDetId is assigned from LDetSales in the Goods & Services record, else it is assigned from LDetPurch.  When Details are journalized, the extended amount (Qty * Each) indicates the amount of debit or credit.  A 'Return Order' or 'Credit Memo' may be indicated by the user entering a negative number for the Qty as the data for the detail is entered.  The Quantity field is used to maintain inventory QOH as details are posted.  It should not be updatable by a u In general, details recording Tender should not show up in the Drop Down list for other details on an order.  If more than one tender is used by an Enterprise the engine's users should be presented with a choice of tender when entering details of tender. At any time, the sum of the extensions (Quantity * Price/Cost Each) of all details posted should total zero.

Detail Id	Order Id	Goods & Services Id	Quantity Exchanged On This Detail	Price or Cost of Each G/S exchanged	Ledger Code for this exchange	Delivery Data: Extra Line, Address, City, State, Zip	Etc/Scheduling, Status/&c/&c....
Auto Incremented Integer	Integer, foreign Key, OrderId	Integer, Foreign Key GS Id existing in Goods & Services table.  Shows what was transferred on this detail	Money or other decimal, agrees with the column in the GS table, quantity of this GS exchanged on this detail.   The sign indicates whether this detail is a Debit (+) or a Credit (-)	Money, cost or price for each unit of this GS at exchange.  If GS Cost or Price is not zero or null use the Cost or Price here.	Integer, Foreign Key, The Id of the Ledger Code assigned to this detail	This may be handled easiest by using separate columns for Name, Street, City, State, and Zip.  This simplifies sending this data to another Enterprise for fulfillment.  If these fields are left null the deliver to data is the same as that of the Entity on the Order.	This can be expanded to include any of 100+ fields as required by an enterprise.

 

Let me know what else you might find essential for a prototype of your order & detail entry systems.  The Ledger Engine's Activity and Ledger Detail tables include the defaults shown on these pages.  If you wish to use other ledger accounts please include the text for the accounts in your Enterprise's JV transactions.