SQL Anywhere database - index two columns

Author

index two columns

ontsnapt

2005-12-21, 1:23 pm

I posted this question in the wrong newsgroup so i post it here again.

I have a table with 2 id's (chips or transponders).

Table animal
Animal_id
Transponder_A
Transponder_B

Transponder A and Transponder B should always be unique. So, if
transponder A is 1000 then transponder 2 can not get the value 1000 and
the other way around. Both can have a Null value.

Is it possible to handle this with an unique index or should I create a
table Transponder that refers to the animal?

The database (ASA 8) runs on a pocket PC and on a desktop

Thanks

Eric

Glenn Paulley

2005-12-21, 1:23 pm

No, a single unqiue index will not enforce the constraint that the two
transponder codes have different values. But a CHECK constraint could
easily do so.

What are you looking for? Constraint enforcement, or query performance
through the index?

Glenn

ontsnapt <ontsnapt@hotmail.com> wrote in news:43a9a216@forums
-1-dub:

> I posted this question in the wrong newsgroup so i post it here again.
>
> I have a table with 2 id's (chips or transponders).
>
> Table animal
> Animal_id
> Transponder_A
> Transponder_B
>
> Transponder A and Transponder B should always be unique. So, if
> transponder A is 1000 then transponder 2 can not get the value 1000 and
> the other way around. Both can have a Null value.
>
> Is it possible to handle this with an unique index or should I create a
> table Transponder that refers to the animal?
>
> The database (ASA 8) runs on a pocket PC and on a desktop
>
> Thanks
>
> Eric
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

ontsnapt

2005-12-21, 8:24 pm

An animal can have two transponders. Transpoinders are allways unique
and can only be used for one animal.

We only use it to find the animal when the transponder is read with an
rfid reader.

so, we use: select animal_id from ... where transponder_A is ... or
transponder_b = ....

Eric

Glenn Paulley wrote:
> No, a single unqiue index will not enforce the constraint that the two
> transponder codes have different values. But a CHECK constraint could
> easily do so.
>
> What are you looking for? Constraint enforcement, or query performance
> through the index?
>
> Glenn
>
> ontsnapt <ontsnapt@hotmail.com> wrote in news:43a9a216@forums
-1-dub:
>
>
>
>
>
>

Glenn Paulley

2005-12-21, 8:24 pm

I'm still having trouble with your requirements - is this a data
modelling question, a performance question, or both?

It seems to me that if the values for transponders A and B come from the
same domain, then you should normalize your schema so that lookups of
animals can occur efficiently with only knowing one value or the other,
and therefore avoid disjunctive queries altogether.

Something like:

Table Animal

- animal ID PK
- other animal data

Table Transponder

- transponder ID PK
- transponder data if any

Table TransponderUsage

- PK: animal ID, transponder ID, transponder code (A or B)
- other data related to this transponder mounted on this animal
- FK: animal ID to Animal
- FK: transponder ID to transponder

Glenn

ontsnapt <ontsnapt@hotmail.com> wrote in news:43a9aee4$1@foru
ms-1-dub:

> An animal can have two transponders. Transpoinders are allways unique
> and can only be used for one animal.
>
> We only use it to find the animal when the transponder is read with an
> rfid reader.
>
> so, we use: select animal_id from ... where transponder_A is ... or
> transponder_b = ....
>
> Eric
>
> Glenn Paulley wrote:
and[color=darkred]
a[color=darkred]
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Andrew Giulinn

2005-12-21, 8:24 pm

Glenn

This is the sort of thing that came to my mind when reading Eric's posts but
won't he need something more to meet his requirement that "Transpoinders are
always unique and can only be used for one animal"?

Is that a unique constraint on "transponder ID" in your TransponderUsage
table?

Andrew

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns9733A317BC53
5paulleyianywherecom
@10.22.241.106...
> I'm still having trouble with your requirements - is this a data
> modelling question, a performance question, or both?
>
> It seems to me that if the values for transponders A and B come from the
> same domain, then you should normalize your schema so that lookups of
> animals can occur efficiently with only knowing one value or the other,
> and therefore avoid disjunctive queries altogether.
>
> Something like:
>
> Table Animal
>
> - animal ID PK
> - other animal data
>
> Table Transponder
>
> - transponder ID PK
> - transponder data if any
>
> Table TransponderUsage
>
> - PK: animal ID, transponder ID, transponder code (A or B)
> - other data related to this transponder mounted on this animal
> - FK: animal ID to Animal
> - FK: transponder ID to transponder
>
> Glenn
>
> ontsnapt <ontsnapt@hotmail.com> wrote in news:43a9aee4$1@foru
ms-1-dub:
>
> and
> a
>
>
>
> --
> Glenn Paulley
> Research and Development Manager, Query Processing
> iAnywhere Solutions Engineering
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288

Pavel Karady

2005-12-22, 7:23 am

Maybe I'm wroing again, but to ensure Transponder_A and Transponder_B
columns uniquity, isn't it enough to state:
UNIQUE ("Transponder_A", "Transponder_B") when creating the table?

CREATE TABLE animal (
animal_id INTEGER PRIMARY KEY,
transponder_a INTEGER,
transponder_b INTEGER,
UNIQUE (transponder_a, transponder_b)
);

This will work only on ASA 8+.

Pavel

"ontsnapt" <ontsnapt@hotmail.com> wrote in message
news:43a9a216@forums
-1-dub...
>I posted this question in the wrong newsgroup so i post it here again.
>
> I have a table with 2 id's (chips or transponders).
>
> Table animal
> Animal_id
> Transponder_A
> Transponder_B
>
> Transponder A and Transponder B should always be unique. So, if
> transponder A is 1000 then transponder 2 can not get the value 1000 and
> the other way around. Both can have a Null value.
>
> Is it possible to handle this with an unique index or should I create a
> table Transponder that refers to the animal?
>
> The database (ASA 8) runs on a pocket PC and on a desktop
>
> Thanks
>
> Eric

Pavel Karady

2005-12-22, 7:23 am

"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43aa7cf6@forums
-1-dub...
> Maybe I'm wroing again

Yes I am :) The uniquity is ensured, but I got confused about what uniquity
means for Mr. Ontsnapt. By stating that the two columns are unique using
(the UNIQUE clause), the 1000 <-> not 1000 is not ensured. And there's more
but it's not worth writing :)

The CHECK constraint may solve this.

Pavel

, but to ensure Transponder_A and Transponder_B
> columns uniquity, isn't it enough to state:
> UNIQUE ("Transponder_A", "Transponder_B") when creating the table?
>
> CREATE TABLE animal (
> animal_id INTEGER PRIMARY KEY,
> transponder_a INTEGER,
> transponder_b INTEGER,
> UNIQUE (transponder_a, transponder_b)
> );
>
> This will work only on ASA 8+.
>
> Pavel
>
> "ontsnapt" <ontsnapt@hotmail.com> wrote in message
> news:43a9a216@forums
-1-dub...
>
>

Breck Carter [TeamSybase]

2005-12-22, 9:23 am

The value pair ( 1, 1 ) would satisfy the UNIQUE constraint as long as
no other row also had ( 1, 1 )... note that other rows could have ( 1,
2 ) and ( 2, 1 ) without violating the UNIQUE constraint.

Also, columns in a UNIQUE constraint cannot be NULL.

The original question used the word "unique" but it sounded like
"different" was the solution being sought. A CHECK constraint could be
used for that.

If the two columns are supposed to be different *AND* unique, two
unique indexes could be used in addition to the CHECK constraint; a
unique index does allow NULL values.

Breck

On 22 Dec 2005 02:16:22 -0800, "Pavel Karady"
<pavel_ns. ns_karady@ns_kogerus
a.com> wrote:

>Maybe I'm wroing again, but to ensure Transponder_A and Transponder_B
>columns uniquity, isn't it enough to state:
>UNIQUE ("Transponder_A", "Transponder_B") when creating the table?
>
>CREATE TABLE animal (
> animal_id INTEGER PRIMARY KEY,
> transponder_a INTEGER,
> transponder_b INTEGER,
> UNIQUE (transponder_a, transponder_b)
> );
>
>This will work only on ASA 8+.
>
>Pavel
>
>"ontsnapt" <ontsnapt@hotmail.com> wrote in message
> news:43a9a216@forums
-1-dub...
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Glenn Paulley

2005-12-22, 9:23 am

Yes.

By splitting the schema up in this way, Eric should have no need to
record the lack of a transponder A or B as a NULL value, but rather as
simply an omitted row - the database records only those transponders that
are in actual use. Hence none of the queries will require disjunctive
predicates, but rather joins (though these should be very efficient).

On the surface, one could amalgamate the Transponder and TransponderUsage
tables that I have below, because TransponderUsage is not in BCNF (Boyce-
Codd Normal Form); TransponderID serves to uniquely identify each row in
the TransponderUsage table, and hence AnimalID and TransponderCode aren't
required in the primary key.

However, splitting up the schema into three tables can have advantages,
depending on other potential requirements that Eric hasn't stated. For
example, if Eric's database is also intended to record the history of any
transponder's usage (potentially on different animals) then keeping the
intersection data in separate tables can make the design more easily
accomdate history data (where timestamps would be added to the
appropriate tables' keys).

These are the tradeoffs that only Eric will be able to judge.

Glenn

"Andrew Giulinn" <NO.oscar.SPAM@NOSPAM.dsa.com.au> wrote in
news:43a9eed1$1@foru
ms-1-dub:

> Glenn
>
> This is the sort of thing that came to my mind when reading Eric's
> posts but won't he need something more to meet his requirement that
> "Transpoinders are always unique and can only be used for one animal"?
>
> Is that a unique constraint on "transponder ID" in your
> TransponderUsage table?
>
> Andrew
>
> "Glenn Paulley" <paulley@ianywhere.com> wrote in message
> news:Xns9733A317BC53
5paulleyianywherecom
@10.22.241.106...
>
>
>

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Sponsored Links