Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 9:15pm, Stef Mientki wrote:

> 1 type of car can (and will) be owned by many persons.

Oh, so these are not specific cars, these are descriptions of cars.   
Yes, in this case you need now what is asked for in the subject-header  
"one to many" but "many to many".  And the correct way to do this is  
to have a table between 'person' and 'vehicle': the one you called  
'bind'.  What you suggested is a fine design and probably what you want.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Shaun Seckman (Firaxis) wrote:
> It seems like the more ideal solution would be to create a third table for 
> ownership that contains a person ID and a vehicle ID.
thanks Shaun,
but that's what I (i'm not a database expert) call the Bind table.
I read some articles about normalization,
and I think I got with this database somewhere to the 5-order?

cheers,
Stef


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Simon Slavin wrote:
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>   
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build- 
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>> 
>
> Well you cannot have one vehicle owned by more than one person.  So  
> you do not need your 'bind' table at all.  You just need to add a  
> column to the vehicle table which contains the person_ID.
>
>   
Sorry Simon,
I expressed myself not well enough:
1 type of car can (and will) be owned by many persons.
But that always the problem with simplifications.
The real problem is about psychological / medical tests and questionnaires,
where a patient for each test scores on a number of scales.
As all scales are of the same type of data, they are all put in one table.


>> I know that every person has 1 car and 1 bike
>> 
>
> Do you mean exactly one car ?  Or up to but no more than one car ?  Or  
> at least one car ?
>
>   
so yes exactly one car, or in reality 1 score on every scale.
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>>
>>
>> I can get the result in the following way:
>>
>> 1. create view Cars: persons join vehicle, where car_or_bike = car
>> 2. create view Bikes: persons join vehicle, where car_or_bike = bike
>> (and change the relevant colum names)
>> 3. join the above view 1 and 2
>>
>> The first problem is that the above approach is quiet slow.
>> Both starting tables (persons and vehicles are also views),
>> and maybe it would be faster to create (temporary) tables ?
>>
>> The second problem is that in the real situation
>> I'm not converting 1 column  to 2 columns,
>> but it can be as large as 1 column to 10 columns.
>>
>> btw I'm running SQLite from Python, so maybe I should do some data
>> handling outside SQL (in Python) ?
>> 
>
> Yes, you are trying to get SQL to do your programming work for you.   
> Just do two SELECT commands in Python: one to find the car for the  
> person, and another to find the bike for the person.  No need to  
> create any temporary tables, create any views, do any joins, or keep a  
> huge table with all the results in.  Much smaller and faster.
>   
Yes but that's not the goal,
the goal is to create a very easy environment,
where even a doctor or nurse can ask any question to the system.

cheers,
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 6:58pm, Shaun Seckman (Firaxis) wrote:

> It seems like the more ideal solution would be to create a third  
> table for ownership that contains a person ID and a vehicle ID.

I thought that a third table was what you proposed in the first  
place.  I was telling you you didn't need one.

>  This will result in more tables (significantly more if you're  
> trying to migrate object oriented data into a relational model) but  
> is that really a bad thing?

Yes.  Every extra table is an additional join, view, or temporary  
table.  In addition it's one more thing that cannot be indexed.  For  
instance, suppose you want to look up all the cars belonging to Person  
#47.  If you have the person number in the vehicle table you can do

SELECT * FROM vehicles WHERE owner = 47 and type = 'car'

and you can make an index on (owner, type) that means the SELECT  
command can go straight there, so it will be super-fast.  Now try  
doing the same thing if you spin your ownership relation off into a  
separate table.  First you have to do a JOIN, then it's impossible to  
create an index, so SQL will have to do searching and sorting to find  
the right records.

> I managed to migrate similar data using this tactic and the biggest  
> issue I've had with it is in the generation of long SQL statements  
> that contain many inner joins (I really wish there was some syntax  
> sugar for automatically joining all foreign keys).  This can become  
> tricky if performance is a priority as I found myself needing many  
> unique SQL statements

Absolutely.  Joins are necessary in many situations.  But joins are  
what makes things like this slow, and the SELECT commands long and  
confusing.  You want to minimise anything that involves joins, views,  
virtual tables, etc..

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

Igor Tandetnik wrote:
> Stef Mientki  wrote:
>   
>> I'm just an amateur  struggling with databases,
>> so forgive if  I ask  a question every database user should know.
>>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type,
>> build-year, etc. The 2 tables are bounded by bind table: person_ID,
>> vehicle_ID
>>
>> I know that every person has 1 car and 1 bike
>>
>> I want to show up a table, with 3 columns:
>>  persons.name  vehicle.brand[car]
>> vehicle.brand[bike]
>> 
>
> select p.name, v1.brand, v2.brand
> from persons p join bind b1 on (p.person_ID = b1.person_ID)
> join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
> v1.car_or_bike = 'car')
> join bind b2 on (p.person_ID = b2.person_ID)
> join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
> v2.car_or_bike = 'bike');
>
> Igor Tandetnik 
>
>   
thanks Igor,

That works very good and fast, and still very readable code.
I just tried it with 1 to 5 columns on the same database
and it's much faster then mine 1 to 2 columns.
I tried some code quiet similar to yours, but got errors,
now I see an essental part is to use aliases for the tables used more 
than once.

thanks!

cheers,
Stef
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Shaun Seckman (Firaxis)]

It seems like the more ideal solution would be to create a third table for 
ownership that contains a person ID and a vehicle ID.  This will result in more 
tables (significantly more if you're trying to migrate object oriented data 
into a relational model) but is that really a bad thing?

I managed to migrate similar data using this tactic and the biggest issue I've 
had with it is in the generation of long SQL statements that contain many inner 
joins (I really wish there was some syntax sugar for automatically joining all 
foreign keys).  This can become tricky if performance is a priority as I found 
myself needing many unique SQL statements.

-Shaun

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of P Kishor
Sent: Wednesday, September 09, 2009 1:35 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] how to get "One to many" in 1 row of a table ?

On Wed, Sep 9, 2009 at 12:32 PM, Simon Slavin
 wrote:
>
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build-
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>
> Well you cannot have one vehicle owned by more than one person.  So
> you do not need your 'bind' table at all.  You just need to add a
> column to the vehicle table which contains the person_ID.


huh! even though my Saturn is a clunker, it is owned by my wife and I,
and until recently, was also owned partially by the credit union via
their auto loan...

..


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Igor Tandetnik]

Stef Mientki  wrote:
> I'm just an amateur  struggling with databases,
> so forgive if  I ask  a question every database user should know.
>
> The situation is like this (simplified):
>
> I've a table with persons: name, address, phone, etc.
> I've another table with vehicles: car_or_bike, brand, type,
> build-year, etc. The 2 tables are bounded by bind table: person_ID,
> vehicle_ID
>
> I know that every person has 1 car and 1 bike
>
> I want to show up a table, with 3 columns:
>  persons.name  vehicle.brand[car]
> vehicle.brand[bike]

select p.name, v1.brand, v2.brand
from persons p join bind b1 on (p.person_ID = b1.person_ID)
join vechicles v1 on (b1.vehicle_ID = v1.vehicle_ID and 
v1.car_or_bike = 'car')
join bind b2 on (p.person_ID = b2.person_ID)
join vechicles v2 on (b2.vehicle_ID = v2.vehicle_ID and 
v2.car_or_bike = 'bike');

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[P Kishor]

On Wed, Sep 9, 2009 at 12:32 PM, Simon Slavin
 wrote:
>
> On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:
>
>> The situation is like this (simplified):
>>
>> I've a table with persons: name, address, phone, etc.
>> I've another table with vehicles: car_or_bike, brand, type, build-
>> year, etc.
>> The 2 tables are bounded by bind table: person_ID, vehicle_ID
>
> Well you cannot have one vehicle owned by more than one person.  So
> you do not need your 'bind' table at all.  You just need to add a
> column to the vehicle table which contains the person_ID.


huh! even though my Saturn is a clunker, it is owned by my wife and I,
and until recently, was also owned partially by the credit union via
their auto loan...

..


-- 
Puneet Kishor
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to get "One to many" in 1 row of a table ?

[Simon Slavin]

On 9 Sep 2009, at 5:15pm, Stef Mientki wrote:

> The situation is like this (simplified):
>
> I've a table with persons: name, address, phone, etc.
> I've another table with vehicles: car_or_bike, brand, type, build- 
> year, etc.
> The 2 tables are bounded by bind table: person_ID, vehicle_ID

Well you cannot have one vehicle owned by more than one person.  So  
you do not need your 'bind' table at all.  You just need to add a  
column to the vehicle table which contains the person_ID.

> I know that every person has 1 car and 1 bike

Do you mean exactly one car ?  Or up to but no more than one car ?  Or  
at least one car ?

> I want to show up a table, with 3 columns:
>  persons.name  vehicle.brand[car]
> vehicle.brand[bike]
>
>
> I can get the result in the following way:
>
> 1. create view Cars: persons join vehicle, where car_or_bike = car
> 2. create view Bikes: persons join vehicle, where car_or_bike = bike
> (and change the relevant colum names)
> 3. join the above view 1 and 2
>
> The first problem is that the above approach is quiet slow.
> Both starting tables (persons and vehicles are also views),
> and maybe it would be faster to create (temporary) tables ?
>
> The second problem is that in the real situation
> I'm not converting 1 column  to 2 columns,
> but it can be as large as 1 column to 10 columns.
>
> btw I'm running SQLite from Python, so maybe I should do some data
> handling outside SQL (in Python) ?

Yes, you are trying to get SQL to do your programming work for you.   
Just do two SELECT commands in Python: one to find the car for the  
person, and another to find the bike for the person.  No need to  
create any temporary tables, create any views, do any joins, or keep a  
huge table with all the results in.  Much smaller and faster.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to get "One to many" in 1 row of a table ?

[Stef Mientki]

hello,

I'm just an amateur  struggling with databases,
so forgive if  I ask  a question every database user should know.

The situation is like this (simplified):

I've a table with persons: name, address, phone, etc.
I've another table with vehicles: car_or_bike, brand, type, build-year, etc.
The 2 tables are bounded by bind table: person_ID, vehicle_ID

I know that every person has 1 car and 1 bike

I want to show up a table, with 3 columns:
  persons.name  vehicle.brand[car]
vehicle.brand[bike]


I can get the result in the following way:

1. create view Cars: persons join vehicle, where car_or_bike = car
2. create view Bikes: persons join vehicle, where car_or_bike = bike 
(and change the relevant colum names)
3. join the above view 1 and 2

The first problem is that the above approach is quiet slow.
Both starting tables (persons and vehicles are also views),
and maybe it would be faster to create (temporary) tables ?

The second problem is that in the real situation
I'm not converting 1 column  to 2 columns,
but it can be as large as 1 column to 10 columns.

btw I'm running SQLite from Python, so maybe I should do some data 
handling outside SQL (in Python) ?

Any suggestions to make it faster and /or more structural will be 
greatly appreciated.

thanks,
Stef Mientki


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users