Re: Designing DB in accordance with the relational model

Okay. Thanks to everybody who answered, I greatly appreciate this! However... Yes, the designing of a DB is more like art than science, but I depicted a quite common situation. So, it MUST have a commonly used solution, right? And no one has explicitly shown this solution yet...
Hm. What would you say if I show you an example universe of discourse + DB attempting to represent it? Could you point at the poor decisions and weak points?

So. Assume there are series of video games in which we can choose from multiple characters. In each game, there is a fixed set of them, and there is a fixed set of characteristics every character has. Some of characteristics are integers, some are strings, some are enums. So, if I need to represent data only about one game from the series, there is no trouble at all -- I define corresponding domains, create relvars and some constraints. As to create relvars, I take this approach: if it is possible for a character to don't have a characteristic, or to have more than one value of it, then I create a separated relvar
{Character# CHAR_ID, CharacterisitcName SOME_TYPE} with Character# as
a foreign key + some other tweaks for primary key. If any character has exactly one value for a characteristic, I put this characteristic into a "main" relvar as an attribute.

TABLE Characters {Character# CHAR_ID, Name CHAR, Type ELEMENT, Attack INT, Defense INT, Special INT, Speed INT}, PRIMARY KEY
{Character#};

TABLE Characters_2nd_Type {Character# CHAR_ID, Type2 ELEMENT}, FOREIGN KEY {Character#} REFERENCES TO Characters, PRIMARY KEY
{Character#};

Pretty simple. Characters have unique Character#, exactly one Name, Type, HP, ..., Speed, and may have one Type2. ELEMENT is enum of strings ("FIRE", "WATER" etc.).

But! We'd like to have info an all games beacuse every character from earlier games also present in later games! And by "present" I mean "Their representations are as close as it's possible".

So, naturally, I'd like to simply add several new relvars and constraints into the DB without touching existing ones (or touching them as little as possible. Yes, they have to be designed with several things in mind, I don't argue with this). But! Let assume that in the second game a) new characters were introduced, b) Characteristic Special has gone (i.e. older characters don't have this characteristic in this game), c) Two new characteristics SpecialAttack and SpecialDefense were introduced (older characters got these characteristics too), d) The domain ELEMENT was extended -- two new values were added; e) Several older characters got new Type2 (not Type! Type2 only).

TABLE CharactersII_NEW {Character# CHAR_ID, Name CHAR, Type ELEMENT2, Attack INT, Defense INT, SpecialAttack INT, SpecialDefense INT, Speed INT}, PRIMARY KEY {Character#};

TABLE CharactersII_ADD {Character# CHAR_ID, SpecialAttack INT, SpecialDefense INT}, PRIMARY KEY {Character#}, FOREIGN KEY
{Character#} REFERENCES TO Characters;

VIEW CharactersII AS Characters{ALL BUT Special} JOIN CharactersII_ADD   UNION CharactersII_NEW;

TABLE CharactersII_2nd_Type {Character# CHAR_ID, Type2 ELEMENT2}, FOREIGN KEY {Character#} REFERENCES TO CharactersII, PRIMARY KEY
{Character#};

(Sorry for long post). This design, however, has a certain flow: There will be new games III, IV, V, and so on. And I can't write a universal query to answer a question "How many characters is in every game?" This is partially because DB doesn't represent the fact that games themselves are things to be taken into regard. Unfortunately, I don't know how to take them into regard.
Second, how do I declare ELEMENT2?

ELEMENT2 = ELEMENT UNION {"NEWELEMENT1", "NEWELEMENT2"}; or

ELEMENT2 = {"WATER", "FIRE", "NEWELEMENT1", "NEWELEMENT2"}; ELEMENT IS ELEMENT CONSTRAINTED {ELEMENT <> "NEWELEMENT1" AND ELEMENT <> "NEWELEMENT2"};

I don't actually know. :( Received on Wed Nov 10 2010 - 20:37:25 CET