Using XML Schemas (XSDs) to Create Database Tables

Introduction

Developers often need to change the type of data source used for Web projects. Deciding to change from XML to a Relational database system is a common task, and means you can base your Websites and applications on a system that is robust, reliable and easy to connect to using Server Side scripting.

Creating your database tables from XML content or XSD Schema Definitions is generally straightforward. In most cases the XML tree structure can be translated into a Relational database structure, with all of the data elements and relationships represented accurately.

Model

Create a model for your database structure by referring to the elements in your XML and XSD. There are many ways to model database structure, but the most common is to simply list your tables on paper. Coming up with a good design before you build your database is essential for efficiency. Look at your XML and XSD Schema, and list all of the elements that have child elements, as the element "person" does in this example:

John Smith
Postman

It is not necessarily the case that these elements will become tables in your database, but this is the starting point for you to figure that out.

Translate

Translate each element of data in your XML into either a table, or a column in a table, by listing each of them in turn. Starting with your list of elements that have child elements, create a draft list of tables.

Initially assume that the elements you listed are all going to become tables, and list their child elements (for example "name" in the "person" element) as columns. List the name of each column and decide on a data type for it, which may be indicated in your XSD as in the following example, where "occupation" is listed as being a text string:

Structure

Check the structure of your tables by making sure every item of data in your XML can be contained. Also check that no items of data have been repeated, as this will result in an unreliable, inefficient data structure. If your data contains any information that you have not listed among your tables and columns, extend your list to include it, considering whether it makes more sense as a new table, or a column within an existing table.

Columns in existing tables are for items that provide additional information about another item, rather than existing as a core item of data in themselves. In the "person" example, "name" and "occupation" would be columns in the "person" table.

Relationships

Look at your list of tables and identify any relationships between them, for example between tables that were child and parent elements in the original XML. If the "person" element was a child of the following example "country" element, "country" would have a table of its own in the database as well:

Canada

John Smith
Postman


Jane Doe
Teacher

The "country" and "person" tables have a "one to many" relationship, meaning that one "country" item can be associated with many "person" items. To reflect this relationship, you need to add a "Foreign Key" column to the list for your "person" table indicating which "country" the "person" is associated with, which you will do when you create your SQL.

Implement

Create your database tables using your chosen Relational Database Management System, either through a Web interface provided by your Web host, or by creating SQL statements according to the following syntax:

CREATE TABLE Country
( Country_ID int, Country_name varchar(50) )

Each table you create should have a column listing an ID as in the above example, and this ID will be included as a "Foreign Key" linking tables as follows:

CREATE TABLE Person
( Person_ID int, Name varchar(50),
Occupation varchar (20),
Country_ref int )

Each record in the "Person" table will include the "Country_ID" listed in the relevant "Country" record, in its "Country_ref" column, creating a relationship between the two. Enter data from your XML into your new database to make sure it can accommodate everything you need.

Notes

• If you find data items in your XML content that do not have a logical home in your database, you may need to make additions to your database design, or to amend it in some way.
• It is not simply the case that elements in XML should be represented as tables in a database, and that their attributes become columns, as this will not always result in the most effective design. Make sure you think carefully about the elements and attributes in your data, and optimize them to suit Relational database structures.

Links

• Fundamentals of Relational Database Design
• XML Schema Simple Elements
• XML Schema Complex Elements
• MySQL 5: Creating a Table
• XML Database Mapping Using XSD (XML Schema) | The Tech Interpreter