[thelist] A SQL request on a SET() field type

[Damien Cola]

> Hi Damien,
>
> IIRC, you need something along the lines of:
>
> SELECT * FROM mytable WHERE 'UK' IN mysetfield;
>
> Which should be work fine, because the DB treats the value of a
> set type as
> an array, not a single value.

Thanks Seb, I tried this and I got a SQL syntax error, I suppose it is
because 'UK' cannot be test against a 'IN'.
It rather has to be the way around, the field IN some values-list..

So I finally got a solution:

Select * From mytable where mysetfield Like '%uk%';

It works fine.

> These types are a compound data structure (in this case, a simple array)
> being held within a single field. This breaks the requirements of
> the "first
> normal form" in an RDBMS data model, which requires (amongst other things)
> that any datum held within your DB field is atomic, ie: it cannot
> be broken
> down into smaller self-contained units.

Yes, it makes sense.

> The reason behind this is that if you wanted to change the value 'UK' to
> 'GB' because your client needs it that way, you have a real data integrity
> issue. If, however, your structure is more like this:
>
> Table_1:
> ID
> OTHER_FIELDS
> Type_ID
>
> Table_2:
> Type_ID
> Type_Name
>
> Then you can restructure your query as:
>
> SELECT Table_1.ID, Table_1.OTHER_FIELDS, Table_2.Type_Name FROM Table_1,
> Table_2 WHERE ( Table_2.Type_ID = Table_1.Type_ID AND
> Table_2.Type_Name='UK' );
>
> If your Type_ID in Table_1 is defined as a foreign key, then this kind of
> select is lightning fast.


Thanks for this explanation, I agree that would be the way to go when
designing a database.

> Of course, you may have better reasons to use a set type, but I
> thought this
> to be worth pointing out. If you've got any questions on DB design, please
> feel free to ask away.

Well, as you guessed it, it is because I want to be able to store 0 to N
values in "mysetfield".. So I set the range ('uk','fr','sp','it', ..) in the
table properties.
And I am able to select one or more countries per data row, in a quite
easily way.


Thank you for the offer.

Damien COLA - Synaptique
Synaptique (UK) Ltd.
Eurogate Business Park
TN24 8XW, Ashford, Kent
Tel: +44 (0)870 777 0026
Fax: +44 (0)870 777 0027
http://www.synaptique.co.uk
Offices: Ashford, Brussels, Helsinki
Milan, Montreal, Paris, Rabat, Toulouse