Simple schema design help

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Simple schema design help

Ian Hardingham
Hey guys.

I have an existing table, the matchTable, where each entry holds a lot
of information about a "match".

I am adding a tournament system, and each match will either be in a
tournament or not in a tournament.

Should I add a "tournamentID" column to matchTable?  Or should I create
a new "tournamentMembershipTable" which holds a simple relationship
between matchid and tournament id?

Thanks,
Ian
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple schema design help

Puneet Kishor-2

On Jun 29, 2011, at 10:35 AM, Ian Hardingham wrote:

> Hey guys.
>
> I have an existing table, the matchTable, where each entry holds a lot
> of information about a "match".
>
> I am adding a tournament system, and each match will either be in a
> tournament or not in a tournament.
>
> Should I add a "tournamentID" column to matchTable?  Or should I create
> a new "tournamentMembershipTable" which holds a simple relationship
> between matchid and tournament id?




If each "match" will belong to only one "tournament" while each "tournament" may relate to more than one "match" then create a "tournatmentID" in the "match" table. If you leave that NULL or, say, 0, then that match will not be in any tournament, else will be in the tournament with that tournamentID.

On the other hand, if each match may relate to zero or more tournaments, and each tournament may relate to zero or more matches, then create a separate tournamentMembershipTable.


>
> Thanks,
> Ian
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple schema design help

BareFeetWare-2
In reply to this post by Ian Hardingham
On 30/06/2011, at 12:35 AM, Ian Hardingham wrote:

> I have an existing table, the matchTable, where each entry holds a lot
> of information about a "match".
>
> I am adding a tournament system, and each match will either be in a
> tournament or not in a tournament.
>
> Should I add a "tournamentID" column to matchTable?

No, at least ideally not, since a large number of the values will be null, which is not a proper normalised design.

> Or should I create a new "tournamentMembershipTable" which holds a simple relationship between matchid and tournament id?

Yes.

Something like:

create table tournamentMembershipTable
( matchID integer primary key not null references matchTable (id) on delete cascade
, tournamentID integer not null references tournament (id) on delete cascade
)

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
--
iPhone/iPad/iPod and Mac software development, specialising in databases
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple schema design help

Ian Hardingham
Hey Tom, many thanks for the help.

At times I will need to identify whether a match is a "tournament match"
or not.  It seems from what you're suggesting that I should do a select
on the tournamentMembershipTable (with zero results being "no") rather
than having a tournamentMatch boolean in the matchTable.

This seems mildly counter-intuitive to me but I'm trying to learn DB design!

Ian


>> I have an existing table, the matchTable, where each entry holds a lot
>> of information about a "match".
>>
>> I am adding a tournament system, and each match will either be in a
>> tournament or not in a tournament.
>>
>> Should I add a "tournamentID" column to matchTable?
> No, at least ideally not, since a large number of the values will be null, which is not a proper normalised design.
>
>> Or should I create a new "tournamentMembershipTable" which holds a simple relationship between matchid and tournament id?
> Yes.
>
> Something like:
>
> create table tournamentMembershipTable
> ( matchID integer primary key not null references matchTable (id) on delete cascade
> , tournamentID integer not null references tournament (id) on delete cascade
> )
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Simple schema design help

BareFeetWare-2
On 30/06/2011, at 8:43 PM, Ian Hardingham wrote:

> Hey Tom, many thanks for the help.

You're welcome.

> At times I will need to identify whether a match is a "tournament match"
> or not.  It seems from what you're suggesting that I should do a select
> on the tournamentMembershipTable (with zero results being "no") rather
> than having a tournamentMatch boolean in the matchTable.

Good question.

You don't need to, and shouldn't count results and then check if it is zero. It's commonly done but it's bad practice and unnecessary. Logically, all you need to ask is if just one match exists or to ask if it appears anywhere in the list, without needing to scan the entire list. Fortunately SQL has two methods to do just that.

select exists (select 1 from tournamentMembershipTable where matchID = theDesiredMatch);

or:

select theDesiredMatch in (select matchID from tournamentMembershipTable);

If your condition requires checking more than one column (which is not the case here), then the exists method is the only option.

Note that since matchID is the primary key, it's already effectively indexed.

> This seems mildly counter-intuitive to me but I'm trying to learn DB design!

Since only some matches will be part of a tournament, it's bad practice (not normalised) to create a foreign key reference column in matches to point to tournament, since it will mostly just have a null value.

It's better to have a separate table, like the one I gave:

>> create table tournamentMembershipTable
>> ( matchID integer primary key not null references matchTable (id) on delete cascade
>> , tournamentID integer not null references tournament (id) on delete cascade
>> )


Which only needs a row for each actual relation (ie each match that is part of a tournament). The "delete cascade" will keep it in sync so that if you delete a match or tournament, the related rows in this table will delete also. Since each match can appear only once in this table, I've set it up to use the table's own primary key column (which is an alias of the always created rowid primary key column) so it doesn't waste overhead with another column it doesn't need.

Remember to turn on:

pragma foreign_keys = YES;

whenever you open a connection to the database. Unfortunately it's off by default.

Tom Brodhurst-Hill
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
[hidden email]
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users