Solved

XML design refering to an existing database.

Posted on 2002-07-17
9
195 Views
Last Modified: 2013-11-19
<?xml version="1.0"?>
<DBSTRUCTURE>
<TABLE>
<NAME>Paper</NAME>
<FIELD>ClassID DATATYPE="3" KEYSTATUS="Foreign Key"</FIELD>
<FIELD>CreatedDate DATATYPE="7" KEYSTATUS=""</FIELD>
<FIELD>Date DATATYPE="7" KEYSTATUS=""</FIELD>
<FIELD>DocFileDir DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>DocFilePath DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>Footer DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>haha DATATYPE="3" KEYSTATUS=""</FIELD>
<FIELD>Header DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>Hours DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>LogoPath DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>MCQSelection DATATYPE="2" KEYSTATUS=""</FIELD>
<FIELD>PaperID DATATYPE="3" KEYSTATUS="Primary Key"</FIELD>
<FIELD>PaperTitle DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>PaperTypeID DATATYPE="3" KEYSTATUS="Foreign Key"</FIELD>
<FIELD>RefCode DATATYPE="202" KEYSTATUS=""</FIELD>
<FIELD>Remarks DATATYPE="203" KEYSTATUS=""</FIELD>
<FIELD>SchoolID DATATYPE="3" KEYSTATUS="Foreign Key"</FIELD>
<FIELD>SemesterID DATATYPE="3" KEYSTATUS="Foreign Key"</FIELD>
<FIELD>TopicID DATATYPE="3" KEYSTATUS="Foreign Key"</FIELD>
<FIELD>UpdatedDate DATATYPE="7" KEYSTATUS=""</FIELD>
</TABLE>
<VIEW>
<NAME>Q Paper</NAME>
<SQL>SELECT Paper.PaperID, School.School, Semester.Semester, PaperType.PaperType, PaperDetail.QLabel, PaperDetail.QNo, PaperDetail.QLevel, QDetail.ColumnFormat, QDetail.Question, QDetail.Location
FROM (School INNER JOIN (Semester INNER JOIN (PaperType INNER JOIN Paper ON PaperType.PaperTypeID = Paper.PaperTypeID) ON Semester.SemesterID = Paper.SemesterID) ON School.SchoolID = Paper.SchoolID) INNER JOIN (QDetail INNER JOIN PaperDetail ON QDetail.QDetailID = PaperDetail.QDetailID) ON Paper.PaperID = PaperDetail.PaperID;
</SQL>
</VIEW>
</DBSTRUCTURE>

The above is some part of a xml file generated from a running program.

What i intend to do is export the table structure (as well as queries/stored procedures) to a xml file. So that next time i can use this xml to regenerate a new database according to the structure of the xml above.

So, please give comments whether the design of the xml above is correct, appropriate or not, so that the regenerate database process will be easier.

Thanks you
0
Comment
Question by:trowa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Expert Comment

by:Carlovski
ID: 7159029
The XML looks o.k (though I don't get what the datatype attribute is storing, do you have some sort of lookup for the datatype codes?), but I can't really see the reason behind doing this, what does storing the database definition in XML gain you over just storing the SQL to generate it?
0
 
LVL 27

Expert Comment

by:BigRat
ID: 7159253
If I were to do this I'd go a bit further and write :-

<FIELD DATATYPE="3" KEYSTATUS="Foreign Key">ClassID</FIELD>

or even :-

<FIELD NAME="ClassID" DATATYPE="3" KEYSTATUS="Foreign Key"/>

and I'd drop empty attributes (KEYSTATUS).

"what does storing the database definition in XML gain you over just storing the SQL to generate it?"

He's not. He is also storing the View was well. And he could expand it to include why's and wherefore's and for whom's.
0
 
LVL 2

Expert Comment

by:Carlovski
ID: 7159398
The sql would also generate the view, and the SQL could be commented for the whys/wherefores.
XML is a great medium for storing and transmitting data to be used in different systems, but for databases there already is a standard, i.e SQL.
I can see how storing table attributes could be useful, the database could be reproduced in proprietry databases which do not use SQL, or use a non standard dialect, but as the XML also contains actual SQL statements, I can't see how that could be used here.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:trowa
ID: 7164245
>>what does storing the database definition in XML gain you over just storing the SQL to generate it?
It's bcos i think the program to generate the SQL (Create Table Statement) will be too complicated (as well as the skill set i have) due to the table design. and i think the Views/Queries can be easily generated using the <View> tag.

BigRat,
Can you tell me which is better and why?

<FIELD DATATYPE="3" KEYSTATUS="Foreign Key">ClassID</FIELD>
or
<FIELD NAME="ClassID" DATATYPE="3" KEYSTATUS="Foreign Key"/>

Sorry guys, i'm new in XML. By the way, thanks for helps.
0
 

Author Comment

by:trowa
ID: 7168777
Anybody can assist here?

Because of the Fields' Property in Table is quite massive, so i need to know which design is better:

FIELD DATATYPE="3" KEYSTATUS="Foreign Key">ClassID</FIELD>
or
<FIELD NAME="ClassID" DATATYPE="3" KEYSTATUS="Foreign Key"/>

Wait for response. TQ.

Increase pts to 75.
0
 
LVL 27

Expert Comment

by:BigRat
ID: 7169073
Sorry, away on a long weekend.

The question - all attributes or all data - can be answered according to the semantics of the data. In principle the methods are identical. Yet the attributes uses less space although the data which can be represented is restricted (no CDATA sections).

So, if the data is not that general and all the "elements" have equal status one uses attributes. Indeed XML from MS SQL Result Sets uses only attributes.

In your case all the "elements" about the field have equal status, so I'd choose only attributes thus :

<FIELD NAME="ClassID" DATATYPE="3" KEYSTATUS="Foreign Key"/>
0
 

Author Comment

by:trowa
ID: 7176639
Last question maybe, as a Field can have many Property/ attribute, so it's the above design can handle it?

What i mean is that:

<FIELD NAME="ClassID" DATATYPE="3" KEYSTATUS="Foreign Key"/ xxx = "ffd" yyy = "ddsdg" zzz = ...>

can be very long..

thanks
0
 
LVL 27

Accepted Solution

by:
BigRat earned 75 total points
ID: 7176841
Yes indeed. But there is actually no limit imposed by the spec, nor any I have found in implementations. I have found this design OK if the following holds :-

1) all attributes of an object have distinct names
2) all attributes have equal semantic status
3) no attribute has child attributes.

where here the word attribute refers to NAME, DATATYPE, KEYSTATUS and the like - not the real attribute names, although they can turn out to be the same.

The first rule eliminates repetition, since that implies a table structure - even if you only have one column! - and that you'd do with elements.

The second rule implies little or no connection between the attributes semantically. If there were you could of course write that in the DTD as a validation criteria (A can only occur with B and not C). But this starts to get messy and is not obvious from just looking at te data. When this starts to occur I use good old elements.

Rule 3 stops us putting structure in an attribute value, for example the attribute consists of a list of things separated by semicolons. It may be the case that the value is indeed such a list, but if the value is just going to  be passed onto some other program or interface and we are not interested in processing the list, then use an attribute. If we are going to dissect the list then elements again.

Lastly there is a precedence. The good old <!element> definition mechanism of the DTD uses attributes for schematic description, not elements!

HTH
0
 

Author Comment

by:trowa
ID: 7184860
Thank you, BigRat. At least, i'm more understand on XML design now. :)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question