universal db schema

Can you comment on the below design. Am I setting myself up for destruction with this sort of design? I have been designing systems over and over again because of completely new requirements that can't be hacked in designs so now I'm looking long term solution to have the most flexible system.

With this design I could dynamically create complexity and designs like below now I realize the implementation won't be straight forward so before I spend days on it I wanted to get some real input.

Is that a common no-no or is that common? Any input would be appreciated.

instance
    firstname   bob
    lastname    gates
    scoreone    20
    scoretwo    90
    scorethree  
        s开发者_如何学CcorethreePart1 30
        scoreThreePart2 32


    CREATE DATABASE uni;
use uni;

CREATE TABLE instance (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT DEFAULT NULL,
FOREIGN KEY         (ParentID) REFERENCES instance(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE keyval_connector (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT NOT NULL,
TextKey                     VARCHAR(255) NOT NULL,
Note                        TEXT DEFAULT NULL,
UNIQUE(ParentID, TextKey),
FOREIGN KEY         (ParentID) REFERENCES instance(ID) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE keyval_int (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT NOT NULL,
Value                       INT DEFAULT NULL,
UNIQUE(Value),
FOREIGN KEY         (ParentID) REFERENCES keyval_connector(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE keyval_varchar (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT NOT NULL,
Value                       VARCHAR(255) DEFAULT NULL,
UNIQUE(Value),
FOREIGN KEY         (ParentID) REFERENCES keyval_connector(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE keyval_double (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT NOT NULL,
Value                       DOUBLE DEFAULT NULL,
UNIQUE(Value),
FOREIGN KEY         (ParentID) REFERENCES keyval_connector(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE keyval_datettime (
ID                          INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
ParentID                    INT NOT NULL,
Value                       DATETIME DEFAULT NULL,
UNIQUE(Value),
FOREIGN KEY         (ParentID) REFERENCES keyval_connector(ID) ON DELETE CASCADE
) ENGINE=InnoDB;

It looks like you're going for an Entity-Attribute-Value (EAV) Model, which has its uses in some situations. Without knowing the specifics of your requirements, it's hard to say if this is a valid use case.

If I understand your schema, what you're doing is building a name/value storage system, with specific tables for the datatypes you're trying to support (dates, numbers etc.)

This is a totally valid approach - but it brings as many problems as solutions. For instance, you can't really model the "relational" part of a database schema in a clean way. Creating queries with even moderately complex logic is tricky - imagine writing something with a few ANDs, ORs and INs. Aggregate functions like MIN, MAX etc. would be very hard to do.

Another problem is that you're working in a different way to the rest of the industry - this makes it hard to bring new developers into the team, or use standard tooling/frameworks such as Object/Relational mapping tools, E/R diagrams etc.

Seems like it'd be fine for storing single key-value pairs. But how would you search for, say, "first name is Fred or Roger and scored at least 23 in a game" without having to do a long series of repeated aliased joins on the same table?

You're basically ignoring the point and purpose of a relational database and using it purely as a key-value store. For that, you might as well go with one of the NoSQL databases.

Without you telling us what application such a database schema is supposed to support, we cannot really comment. In general, however, the DB schema is supposed to at least somewhat reflect the application and the data of one or more specific domains.

However, inserting one more layer just in order to avoid future refactoring does not sound very appealing to me, at least not without a clear-cut advantage for the code that is under development now, such as introducing a more suitable storage model for your data.

As it is, all I can see is a whole bunch of indexes (implied by the PRIMARY KEY and UNIQUE constraints) and foreign keys, which is an excellent way to bring any DB server down to its knees...

As Joe mentioned, it looks like you are creating an EAV model. I think this works best for sparse data. So, try to keep things that are common to all entities (first name, last names, etc.) in a single table, then use the EAV model for the more sparse data or for attributes that are more likely to change over time.

Although I can sympathise with why you are thinking along these lines, you are probably setting yourself up for a world of pain. Relational databases are for structured data and are designed to manipulate it efficiently.

Have you looked at XML databases

http://www.ibm.com/developerworks/library/x-comparexmldb/

I'm not recommending any particular database because I don't know enough about them