CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-databasedatabase-schema
Zharro
Zharro

Reputation: 809

How to design a database that is to change often?

I inherit a project of a program that configures devices via ethernet. Settings are stored in the database. The set of settings is constantly changing as devices are developing so there's a need for a simple schema change (user must be able to perform this operation).

Now, this simplicity is achieved by the XSD-scheme (easy readable and editable), and the data is stored as XML. This approach also satisfies the requirement of the use of various database engines (MS SQL and Oracle are currently supported).

I want to move database structure to the relational model. Are there any solutions which are as easy-to-change as described one while using a relational database?

Upvotes: 0

Views: 534

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52157

I want to move database structure to the relational model.

Why?

Do you want to be able to index/query parts of the configuration, or be able to change just one part of the configuration without touching the rest?

  • If no, then just treating the XML as opaque BLOB should be sufficient.
  • If yes, then you'll have to tell us more about the actual structure of configuration.1

1 BTW, some DBMSes can "see inside" the XML, index the elemnts and so on, but that would no longer be DBMS-agnostic.

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33283

There are several solutions to your design problem. I suggest the following;

  1. Use a different database. Relational databases are not the best choice for this kind of data. There are databases with good support for dynamic data. One example of such a database is mongoDB, which uses JSON-style documents.

or 2. Create one (or a small set) of Key/Value table(s). You can support a hierarcical structure by adding a parent column that points to the parent key-value pair.

I wouldn't recommend changing a relational db schema on the fly as the result of a user operation. It goes against fundamental design rules for relational database design.

Upvotes: 0

Related Questions