I need to design a database in which the columns have...
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   amit_jain
Posted On:   Monday, October 30, 2000 10:19 AM

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?

Re: I need to design a database in which the columns have...

Posted By:   Luigi_Viggiano  
Posted On:   Tuesday, May 15, 2001 01:48 AM

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:



prop1value
prop2value


...


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.
About | Sitemap | Contact