[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

XML design refering to an existing database.

Posted on 2002-07-17
9
Medium Priority
?
199 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 300 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

Independent Software Vendors: 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!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

656 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