Hello All,
I have 3 entities:
1. town
2. person
3. alliance
Links between entities:
person owns the towns
group of persons can unites into alliance
CREATE TABLE town (
tid int, /* town id */
pid int, /* owner person id */
name char,
...
);
CREATE TABLE person (
pid int, /* person id */
aid int, /* alliance of this person */
name char,
....
);
CREATE TABLE allaince (
aid int, /* alliance id */
name char,
...
);
Here is events:
one person can capture town from another person
person can change alliance to another or stay without it
Task:
Keep events in SQL DB in most appropriate format for handy view events
for one person/alliance
Currently i know two way of solving this task:
1. Simple create table like this:
CREATE TABLE event (
stamp DATETIME, /* time of event */
type int, /* type of events, for ex. 1 - capture town, 2 -
alliance change */
tid int, /* town id */
pid int, /* (old) person id */
aid int, /* (old) alliance id */
new_pid int, /* (new) person id */
new_aid int /* (new) allaince id */
);
So, if i want to view all events for alliance, query should be like
this:
SELECT * from event WHERE aid = $AID OR new_aid = $AID;
and next i should resolve any tid/pid/aid into real names ...
This solution is very simple and don't look very nice in
retrospective of Normal Forms :/
2. Very compact variant.
For keep all events enough this simple table:
CREATE TABLE event (
stamp DATETIME,
type int, /* 1 - capture, 2 - alliance change */
entity_id int, /* should be tid if it's capture, or pid if it's
alliance change */
old_id int /* should be pid if it's capture, or aid if
it's alliance change */
);
for example capture of village v1 by person p1 from person p2 will
looks like:
(stamp1, 1, v1, p2) and so on...
This variant requires many queries and program logic if i want to see events for
alliance/player, BUT this construction looks very geek :-)
Maybe here is some other solution? Not so stupid like 1 variant and not
so geek like 2 variant?
--
Biomechanical Artificial Sabotage Humanoid
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
