Solved

XML design refering to an existing database.

Posted on 2002-07-17
9
185 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 2

Expert Comment

by:Carlovski
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:trowa
Comment Utility
>>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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:trowa
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you, BigRat. At least, i'm more understand on XML design now. :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
c#, split 12 77
MVC and Angular 2 70
Form submit issue 11 51
Detect file exist or not 3 74
What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now