On 14/06/2011, at 8:29 PM, Ian Hardingham wrote:
> Guys, I have another DB design question.
Have you implemented the normalisation we discussed before, yet, especially id
integer primary key column for players? Otherwise, stop here.
> I have 100 - 10000 clients connected to my server.
Is a client the same as a player, people and user? I'll assume so.
> Each client has a status. Many clients are "watching" *one* other
> client, which means that any change in that client's status must
> immediately be sent to the watching clients.
>
> Estimates of numbers:
>
> - 70% of people online will be watching someone
> //
> - 50% of people will be watched by 0 clients
> - 40% of people will be watched by 1 client
> - 9% of people will be watched by 2 clients
> - 1% of people will be watched by 3 or more clients
>
> Here's my attempt at a schema:
>
> - because your status is changing often I would have a separate table,
> clientStatusTable, which would hold client ids and their status
Does every player/client have a status (one and only one)? If so, you could
just keep the status as a column in the player/client table. But a separate
clientStatusTable would be OK, possibly immeasurably slower. In either case, I
suggest making a separate status table and using foreign key references to it,
rather than storing the text of the status repeatedly for each player.
> - I would have another table, clientWatchingTable, which would store
> relations between clients and who they are watching. I would probably
> index on watched client because I would need to select everyone watching
> a client often
>
> Does this seem like a sane approach?
Yes, that seems like a good approach, as long as you've normalised the
player/user/client table already. Then you'd have something like this:
create table status
( id integer primary key not null
, name text not null unique collate nocase
)
;
create table user
( id integer primary key not null
, name text not null unique collate nocase
, email text collate nocase
, key text
, status integer references status(id)
, other columns
)
;
create table clientWatchingTable
( id integer primary key not null
, watcher references user (id)
, watching references user (id)
)
;
create index clientWatchingWatcherIndex on clientWatchingTable (watcher)
;
And you could get the name and statuses of all users being watched by a
particular user by:
select watchingUser.name
, status.name as status
from clientWatchingTable
join user on watcher = user.id
join user as watchingUser on watching = user.id
join status on watchingUser.status = status.id
where watcher = ?
;
Tom Brodhurst-Hill
BareFeetWare
--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
