General Design Question

Hey

I need to store something a little different in a DB and I was hoping one of
you guys might be able to help me.

Basically it represents a 'world'. I have an initial state and then I get
info like this...

27/11/03 17:21 Mary is born
27/11/03 17:21 Dave is born
27/11/03 17:22 Sean is born
27/11/03 17:23 Peter dies
27/11/03 17:23 Fred is born

I need to be able to run querys like this...

How many people are alive at 27/11/03 17:22
Who was born between 27/11/03 17:22 and 27/11/03 17:23
etc.

Problem is, I'm going to have hundres of 'world's each with thousands of
entrys.

All help is appreciated :)

Tnx

Naomi"Naomi Morton" <dopey_delete@.remove.iol.ie> wrote in message
news:1069954180.280897@.emeairlvalid.ie.baltimore.c om...
> Hey
> I need to store something a little different in a DB and I was hoping one of
> you guys might be able to help me.
> Basically it represents a 'world'. I have an initial state and then I get
> info like this...
> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born

Perhaps something like this:

CREATE TABLE Worlds
(
world_id INT NOT NULL PRIMARY KEY
)

CREATE TABLE Persons
(
world_id INT NOT NULL REFERENCES Worlds (world_id),
person_name VARCHAR(25) NOT NULL,
birth_datetime DATETIME NOT NULL,
death_datetime DATETIME NULL, -- NULL if still alive
CHECK (death_datetime >= birth_datetime),
PRIMARY KEY (world_id, birth_datetime, person_name) -- simplification
)

> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22

DECLARE @.alive_at_datetime DATETIME
SET @.alive_at_datetime = '20031127 17:22'
SELECT world_id, COUNT(*) AS alive_at_datetime
FROM Persons
WHERE birth_datetime <= @.alive_at_datetime AND
(death_datetime IS NULL OR death_datetime > @.alive_at_datetime)
GROUP BY world_id

> Who was born between 27/11/03 17:22 and 27/11/03 17:23

DECLARE @.start_datetime DATETIME, @.end_datetime DATETIME
SET @.start_datetime = '20031127 17:22'
SET @.end_datetime = '20031127 17:23'
SELECT world_id, person_name, birth_datetime
FROM Persons
WHERE birth_datetime BETWEEN @.start_datetime AND @.end_datetime

> etc.
> Problem is, I'm going to have hundres of 'world's each with thousands of
> entrys.

Millions of rows should not present a problem at all.

Regards,
jag

> All help is appreciated :)
> Tnx
> Naomi|||> 27/11/03 17:21 Mary is born
> 27/11/03 17:21 Dave is born
> 27/11/03 17:22 Sean is born
> 27/11/03 17:23 Peter dies
> 27/11/03 17:23 Fred is born
> I need to be able to run querys like this...
> How many people are alive at 27/11/03 17:22
> Who was born between 27/11/03 17:22 and 27/11/03 17:23
> etc.

Hi Naomi,

What you have is similar to banking transaction data. For example,
27/11/03 17:21 customer #1 debited $100 from his checking account. In
this case, the entity in question are individual accounts.

I assume you're creating a fantasy gaming world. The entity in
question are the character "avatars". To make a long story short, you
should have a WORLD table and an AVATAR table. The avatar is
populated by your journal transaction entries and should have worldID,
avatarID, birth, and death columns.

To query how many are alive:
select count(*) from avatar where death < @.death or death is null and
worldID=@.worldID

To query who was born between @.start and @.end:
select * from avatar where worldID=@.worldID and birth between @.start
and @.end

-- Louis