Re: Designing DB in accordance with the relational model

On Wed, 10 Nov 2010 15:54:26 -0800 (PST), Kentauros <joker.vd_at_gmail.com> wrote:

>On Nov 11, 12:39 am, Hugo Kornelis
><h..._at_perFact.REMOVETHIS.info.INVALID> wrote:
>>
>> Since some elements don't exist in all games, the obvious predicate to
>> include in your model would be "<Element> exists in <Game>".
>
>Well, it seems to be reasonable solution. Maybe "<Element> exists
>starting from <Game>" would be even better. Although it means I cannot
>rely on domain constraints... well, DCNF is pretty useless anyway, so
>actually there is no big problem.

I don't see why this would be better. Unless you are 100% sure that elements are only added and never removed. And even in that case, there is nothing wrong with sticking to "<Element> exists in <Game>", and it will probably make your queries easier.

Also (and I'll probably regret asking and slap my forehead when you reply), what is DCNF? I could not find any plausible entry in wikipedia, wiktionary, dictionary.com, or onelook.com.

>> The predicate "<Character> has <Defense>", for instance, is
>> incomplete. To complete it, you have to change it to "<Character> has
>> <Defense> in <Game>".
>
>The problem is, many characteristics never change at all.

But why is that a problem?

"Wario has defense 17 in Super Mario World"
"Wario has defense 17 in Super Mario World 2"
"Wario has defense 17 in Super Mario World 3D"
"Wario has defense 17 in Super Mario World - the last part"
"Wario has defense 17 in Super Mario World - the sequel to the last

There is nothing wrong with this extension. The candidate key for this relvar is (Character, Game), and there are no violations of this key, or of any other constraint.

From your posts, I get the impression that you try to minimize the number of distinct facts in the database. In my experience, it is often far better to minimize the number of distinct predicates. If you are concerned about the amount of work involved with entering the same information over and over again, then don't forget that the UI can handle that kind of work. Let the UI create a new game as a clone of its predecessor, then the users only need to enter the new and changed characteristics. But the UI discussion is of course way off-topic here.

(snip)
>TABLE CharactersExistence{Character# CHAR_ID, CharacterName CHAR,
>Game GAME}
> PRIMARY KEY {Character#, Game},
> FOREIGN KEY {Game} REFERENCES TO Games;

I'm wondering why you have introduced a character number, and why the CharacterName is not a candidate key. Since this is all about tracking different characteristics of the same character in different games, I would have expected the character name to be the most obvious identifier for characters - or are there indeed any games with a "Sonic" character that is not derived from the original hedgehog?

(snip)
>But it seems that the table Characters has to go — characters have
>different sets of characteristics in different games, and I can't
>predict what will (dis)appear or which remained characteristic change
>-- except for name.

But why would that impact the table Characters? If a character exists in a game, you can represent that fact in this table, regardless of his/her characteristics.

If your UoD ensures that each character in each game has at least one characteristic (and only then), you could consider regardig this table as redundant and dropping it - though I'd probably still keep it, along with appropriate foreign keys. (You would not drop the table Orders because each order has at least one OrderItem either, would you?)

> I think I'd want to have a relation with predicate
>like "Characteristic <Name> with type <Type> existed in game <Game>",
>and I know how to do this... but I'm afraid of it and won't do it.

The same logic applies here as before - though the existence of a characteristic in a game is implied by characters from that game having it, it does not hurt to be able to store that fact explicitly.

>TABLE CharacterSpecial {Character# CHAR_ID, Game GAME, Attack INT}
> PRIMARY KEY {Character#, Game},
> FOREIGN KEY {Character#, Game} REFERENCES TO CharactersExistence,
> CONSTRAINT IS_EMPTY (CharactersExistance {Character#, Game} WHERE
>Game = GAME("First Game") XMINUS CharacterSpecial{Character#,
>Game}); // Special is mandatory in the first game, but is absent in
>all the later ones.

This is not a good constraint. What will you do if a new game is relased next month (just in time for Christmas), where special is mandatory again? Change the constraints?

If special is mandatory on one game and optional in another, than you can do one of two things:
1) Leave it optional - after all, there clearly is no generic rule that each character must have special in each game. 2) Add the unary preciate "In <Game>, special is mandatory" to your model and use that instead of the name of the first game in the constraint.

>Also I might need some other constraints to enforce, umm, the
>"mandatoriness" of several characteristics in certain games and the
>absence of several others in certain games. But this design is as ugly
>as hell.

Yup. That's what you get when you mix data and metadata (as you did in the constraint for the "sometimes-mandatory" special characteristic.

I'm also wondering about your choice to use seperate tables for each characteristic (attack, defense, special, ...). Not that this is really WRONG, but it might not be the best choice either. Have you considered the predicate "<Character> has <Characteristic> <score> in <Game>" to replace all these? (So basically, move the characteristic from the metadata level to the data level, so that you have less tables with more rows). In a later post, you mention the need to find all characteristics for a character in a game - with this design, that's a breeze.

-- 
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis