-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 5/21/2014 11:09 AM, Stephan Beal wrote:
> On Wed, May 21, 2014 at 6:00 PM, Humblebee
> <fantasia.d...@gmail.com> wrote:
>> | 1 | 4 | 1,5,2,3,4 | | 2 | 5 |
>> 2,6,3,5,1 |
>
> Without doing what Simon suggests, there is no good solution to
> your problem with the data structure you have. SQL is made for
> normalized data, not strings containing arbitrary tokens separated
> by arbitrary other tokens.
It's probably been suggested at least once in response to the thousands
of times this same question has come up on the list, but I'll put it
forward again. Perhaps create virtual tables which are essentially
views onto this table but with a normalized presentation.
Something like:
CREATE TABLE TeamAssignment (
parId INTEGER NOT NULL REFERENCES TeamTable,
personId INTEGER NOT NULL REFERENCES PersonTable,
PRIMARY KEY (parId, personId)
);
If you had this, you'd be able to associate persons with their teams,
including situations where a team is empty and a person is in many
teams.
Going the virtual table route will have a negative impact on performance
and maintainability, compared to actually normalizing your database.
But you say you're stuck, so do what you have to. And honestly, please
don't give people with no knowledge of SQL theory the power to set your
SQL schema in stone.
- --
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQEcBAEBAgAGBQJTfNSYAAoJELtYwrrr47Y4GToH/3uXzvBNY499X3nfSEsWPcR4
42o1W/dGRvdwi6p4hGjZmL55RhHwpTjljHaupszF1o3SB6nGlBDaxeOD86SYMATC
KV8w8aNxaFmo6SnwaMmLTOKfL5qnFqqcEV6FD4wpIbSaziteG39AenN4kQqIYAbH
Dpk0XULlrRQClRP+77CWQvdodWzK0C9YEkNCaCYgez6MrvfDOpvTz8s83aKsOSCS
QGGfwHUuwFg96tf1jVYi0PUOiZHofWBXaRESP59uFAO3cRiuOEydHSbabjufexub
hf4ubOsfqlLnJuWhoWXNBzMGDRe2Iu8v46iNR5CT2c4/nzjjkESsKW05xxC86oM=
=c5Bu
-----END PGP SIGNATURE-----
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
