[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

XML design refering to an existing database.

<?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
trowa
Asked:
trowa
  • 4
  • 3
  • 2
1 Solution
 
CarlovskiCommented:
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
 
BigRatCommented:
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
 
CarlovskiCommented:
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.

 
trowaAuthor Commented:
>>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
 
trowaAuthor Commented:
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
 
BigRatCommented:
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
 
trowaAuthor Commented:
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
 
BigRatCommented:
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
 
trowaAuthor Commented:
Thank you, BigRat. At least, i'm more understand on XML design now. :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now