I need to design a database in which the columns have to be assigned dynamically whenever a user defines a new attribute. Is it possible to have a flexible database column design using XML, and an Oracle/SQL Server? How?

Created May 7, 2012

Luigi Viggiano

About the database, you can design it like this:


CREATE TABLE "MY_OBJECT" 
(
  "ID"	INTEGER NOT NULL,
  "PROPERTY_1"	CHAR(30),
  "PROPERTY_2"	CHAR(30),
  PRIMARY KEY ("ID")
);

CREATE TABLE "EXTERNAL_ATTRIBUTE" 
(
  "ID"	INTEGER NOT NULL,
  "OBJECT_ID"	INTEGER NOT NULL,
  "NAME"	CHAR(30) NOT NULL,
  "VALUE"	CHAR(255),
 PRIMARY KEY ("ID")
);

ALTER TABLE "EXTERNAL_ATTRIBUTE" 
ADD FOREIGN KEY ("OBJECT_ID") 
REFERENCES MY_OBJECT ("ID");

your XML can be similar to this:


<?xml version="1.0"?>
<my_object>
  <property_1>prop1value</property_1>
  <property_2>prop2value</property_2>
  <external_attribute name="att1Name" value="att1Value" />
  <external_attribute name="att2Name" value="att2Value" />
   ...
</my_object>

Each "my_object" instance is stored in a record of MY_OBJECT table, and each "external_attribute" is stored in a record of EXTERNAL_ATTRIBUTE table with an OBJECT_ID field that chain it to the referenced object. IDs are not present in the XML because them should be managed by the application.