General Design Question

[Naomi Morton]

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

[John Gilson]

Re: General Design Question

"Naomi Morton" <dopey_delete@r emove.iol.ie> wrote in message
news:1069954180 .280897@emeairl valid.ie.baltim ore.com...[color=blue]
> 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[/color]

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
)
[color=blue]
> I need to be able to run querys like this...
>
> How many people are alive at 27/11/03 17:22[/color]

DECLARE @alive_at_datet ime DATETIME
SET @alive_at_datet ime = '20031127 17:22'
SELECT world_id, COUNT(*) AS alive_at_dateti me
FROM Persons
WHERE birth_datetime <= @alive_at_datet ime AND
(death_datetime IS NULL OR death_datetime > @alive_at_datet ime)
GROUP BY world_id
[color=blue]
> Who was born between 27/11/03 17:22 and 27/11/03 17:23[/color]

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
[color=blue]
> etc.
>
> Problem is, I'm going to have hundres of 'world's each with thousands of
> entrys.[/color]

Millions of rows should not present a problem at all.

Regards,
jag
[color=blue]
> All help is appreciated :)
>
> Tnx
>
> Naomi[/color]

[louis nguyen]

Re: General Design Question

> 27/11/03 17:21 Mary is born[color=blue]
> 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.[/color]

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=@worldI D

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

-- Louis