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 [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
