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]

"Naomi Morton" <do**********@remove.iol.ie> wrote in message
news:10***************@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

[louis nguyen]

> 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