Review an XML n00b's Schema

[mikeycorn]

I've got so many questions about different aspects and if I'm doing it right or not, but maybe I'll just post my attempt at writing an XML database and hopefully someone will be kind enough to come along and tell me if there are some things that need changing before it can be called a good example of XML:

<?xml version="1.0" encoding="iso-8859-1"?>

<Customers>
<Customer>
<CustomerID="CID-0001">
<FName>John</FName>
<LName>Drake</LName>
<Add1>14221 Eagle St.</Add1>
<Add2>Anaheim, CA 94738</Add2>
<Add3></Add3>
<Email>thedrakester@bunghole.com</Email>
<Phone>714-797-7453</Phone>
<TypicalAmountDue></TypicalAmountDue>
<Notes></Notes>
<Current>No</Current>
</Customer>
<Customer>
<CustomerID="CID-0002">
<FName>Shiela</FName>
<LName>Simpson</LName>
<Add1>924 Monterry St.</Add1>
<Add2>Homerville, FL 43792</Add2>
<Add3></Add3>
<Email>shielas@nowhere.com</Email>
<Phone>526-426-8563,526-426-4921</Phone>
<TypicalAmountDue></TypicalAmountDue>
<Notes></Notes>
<Current>Yes</Current>
</Customer>
<Customer>
<CustomerID="CID-0003">
<FName>David</FName>
<LName>Blaine</LName>
<Add1>40401 Starvation Rd.</Add1>
<Add2>Apt. 101</Add2>
<Add3>Weirdoton, AL 39412</Add3>
<Email>davidb@maninabox.com</Email>
<Phone>no phone</Phone>
<TypicalAmountDue>$5,000</TypicalAmountDue>
<Notes>needs a cheeseburger</Notes>
<Current>Yes</Current>
</Customer>
</Customers>

<BillPeriods>
<BillPeriod>
<BPName>January 2004</BPName>
<BPStart>2004/01/01</BPStart>
<BPEnd>2004/01/31</BPEnd>
<BillSent>2004/01/01</BillSent>
</BillPeriod>
<BillPeriod>
<BPName>February 2004</BPName>
<BPStart>2004/02/01</BPStart>
<BPEnd>2004/02/29</BPEnd>
<BillSent>2004/02/02</BillSent>
</BillPeriod>
<BillPeriod>
<BPName>March 2004</BPName>
<BPStart>2004/03/01</BPStart>
<BPEnd>2004/03/31</BPEnd>
<BillSent>2004/03/01</BillSent>
</BillPeriod>
</BillPeriods>

<InvoiceItems>
<InvoiceItem>
<InvoiceItemID="II-0001">
<ItemPrice>$80.00</ItemPrice>
<ItemDescription>Comprehensive Monthly Pool Service<ItemDescription>
</InvoiceItem>
<InvoiceItem>
<InvoiceItemID="II-0002">
<ItemPrice>$80.00</ItemPrice>
<ItemDescription>Monthly Spa Service<ItemDescription>
</InvoiceItem>
</InvoiceItems>

<InvoiceRecords>
<InvoiceRecord>
<InvoiceBP>January 2004</InvoiceBP>
<InvoiceCustomerID>CID-0001</InvoiceCustomerID>
<InvoiceItem1>II-0001<InvoiceItem1>
<InvoiceItem2>II-0002<InvoiceItem2>
<InvoiceItem3><InvoiceItem3>
<InvoiceItem4><InvoiceItem4>
<InvoiceItem5><InvoiceItem5>
</InvoiceRecord>
</InvoiceRecords>

 

[jel]

I'm not quite sure what are you are building or how we can help you, so just a small remark:

If you use <InvoiceItem id="1"/><InvoiceItem id="2"/> etc,
you can choose lateron how many there are going to be, and have records with 1, 2 and 204 items in them.

I'd keep prices in numbers, so <TypicalAmountDue>5000</TypicalAmountDue>
instead of
<TypicalAmountDue>$5,000</TypicalAmountDue>

 

[mikeycorn]

Thanks, Jel. Two good suggestions there.

It's an invoice program with primary keyed tables for Customers, BillPeriods and InvoiceItems, all three of which imported (?) as foreign keys to the InvoiceRecords table which keeps track of what each customers was invoiced for each bill period and what each customer's balance is for that billing (forgot to include that in my post.)

It's not only my first XML database, but my first relational database as well, but I'm pretty sure I've got a normalized layout. (The terminology still doesn't feel natural yet, but I think I'm talking the talk there.)

The main questions that come to mind:

Am I right in adding, for example, <notes></notes> when there's nothing in that field, just to kind of keep some sort of a placeholder there?

The BillPeriod table's BPName field won't allow any duplicate entries, so am I right in assuming then that there's really no need for a BillPeriodID field there?

How come I see things like:

<CustomerID="CID-0001">

all between the < and the > or even the name of the table and the CustomerID like:

<Customer CustomerID="CID-0001">

when all the other fields usually work like:

<FirstName>Joe</FirstName>



When my Primary Keys of <CustomerID> and <BPName> are imported as Foreign Keys in the InvoiceRecord table, is it better to rename them as I did, <InvoiceCustomerID> and <InvoiceBP> or would it be better to give the foreign key fields the same names they have as primary keys in their . . . uh, the tables from which they came. (Lacking terminology there.)



Finally, like you said to keep the ID's open ended instead of locked into a number of digits like 0001, is there a way to keep my InvoiceItems in the InvoiceRecord open ended as well? For example, I was using <InvoiceItem1> through 5, giving it a neat orderly five fields for invoicable items in every recordset, but is there a way that that too could be left open ended, i.e. can I be adding an InvoiceItem6 to one recordset without appending a InvoiceItem6 to every recordset in the table?



Lots of questions, I know, but who knows, maybe someday you'll have to make sense of my XML and I don't want you to have to scratch your head in confusion over it now do I?

 

[jel]

Am I right in adding, for example, <notes></notes> when there's nothing in that field, just to kind of keep some sort of a placeholder there?
Usually the xml is generated one way or another, and this usually adds empty nodes where there is no content. It doesn't really matter. The definition of what nodes are allowed in your xml can be made with schema or DTD, but that's quiet another chapter.

The BillPeriod table's BPName field won't allow any duplicate entries, so am I right in assuming then that there's really no need for a BillPeriodID field there?
On database-level I like numeric ID's very very much. However, wether or not to put then in your xml just depends on whatever you'r going to do with this xml.

How come I see things like:<CustomerID="CID-0001"> etc
Now there's a good question which I completely overlooked.
<CustomerID="CID-0001"> is not xml.
You can either have:
<Customer ID="CID-0001">blabla</Customer>
or
<Customer>
<ID>CID-0001</ID>
blabla
</Customer>
That is: either an attribute or a sub-node. There are different opinions on which is to be preferred. In most cases, anything you want to do can be done with both solutions.
In general, people tend to put id's and stuff like that in attributes, and values that should be displayed in the node-value. Something like:
<person id="12">
<name>Karl</name>
</person>

 

[jel]

Finally, like you said to keep the ID's open ended instead of locked into a number of digits like 0001, is there a way to keep my InvoiceItems in the InvoiceRecord open ended as well? For example, I was using <InvoiceItem1> through 5, giving it a neat orderly five fields for invoicable items in every recordset, but is there a way that that too could be left open ended, i.e. can I be adding an InvoiceItem6 to one recordset without appending a InvoiceItem6 to every recordset in the table?

Yes, you can add nodes with the name as often as you want. In that case you could use attributes to keep them apart (if needed).
<InvoiceRecords>
<InvoiceRecord>
<InvoiceItem Number="1">BLA</InvoiceItem>
<InvoiceItem Number="2">BLA_ONCE_AGAIN</InvoiceItem>
</InvoiceRecord>
<InvoiceRecord>
<InvoiceItem Number="1">ONCE_BLA</InvoiceItem>
</InvoiceRecord>
<InvoiceRecord>
<!-- no itmes -->
</InvoiceRecord>
</InvoiceRecords>

 

[mikeycorn]

Excellent, thanks Jel. Now I think I understand the difference between attributes (internal data, right?) and values. Very good.

I never really imagined that my sub-nodes could have attributes just like the recordsets, that's a very nice way of being able to expand how many InvoiceItems each customer will have.

Right now it's really just a single-user desktop app, so the question could be asked, "why would you want to go to the trouble of saving the data in XML and parsing it back to the program when it's only going to be used locally by single users?"

I'm not even sure I know what has inspired me to make this little desktop app native XML, but I really just like the way XML is structured, I like the fact that you can open it up and view the contents, and I have a feeling there will be benefits down the road that I can't forsee.

Who knows, maybe as XML becomes more and more prevalent, maybe the benefits down the road are still unforeseen by those who are deeply-involved with databases.