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.