On Wed, 6 Sep 2006 07:53:30 +0000 "Arnar Birgisson" <[EMAIL PROTECTED]> wrote:
> On 9/6/06, Enrico Morelli <[EMAIL PROTECTED]> wrote: > > On Tue, 5 Sep 2006 16:11:43 +0000 (UTC) > > Tony Theodore <[EMAIL PROTECTED]> wrote: > > > > > Enrico Morelli <morelli <at> cerm.unifi.it> writes: > > > > > > > > > > gg | integer | not null > > > > day | character varying(3) | > > > > av1000 | character varying(50) | > > > > av200 | character varying(50) | > > > > av400 | character varying(50) | > > > > av500 | character varying(50) | > > > > av600 | character varying(50) | > > > > av700 | character varying(50) | > > > > av700b | character varying(50) | > > > > av700wb | character varying(50) | > > > > av800 | character varying(50) | > > > > av900 | character varying(50) | > > > > relaxometer | character varying(50) | > > > > av1200 | character varying(50) | > > > > av1300 | character varying(50) | > > > > > > Not directly related to this problem, but have you thought about > > > normalizing the the table instead of altering it? > > > > > > Tony > > > > > > > Thanks Tony, > > > > but I don't understand your suggestion, sorry. The > > normalization is a process that eliminates redundancy, organizes > > data efficiently, reduces the potential for anomalies during data > > operations and improves data consistency (from wikipedia). > > > > Do you see some anomalies or other strange things in my table? > > > > How can I normalize the table instead of altering it to add a new > > instrument to the existing table (I'm using postgre)? > > Tony is correct, in general, this is not good db design. By > normalizing this design you would keep a seperate table for your > instruments, so adding an instrument would only be the matter of > inserting into that table. > > Also, a seperate table for each year would be normalized as one table > with a column for the year. Saves you from creating a new table yeach > year. > > Probably something like this: > > instruments: > instrument_id (pk) > instrument_name > > planning_sets: > year (pk) > dd (pk) -- is this the month? > day (pk) > instrument_id (pk) > data varchar(50) -- this col contains whatever you used to put in he > instrument columns in planning_sets previously > > Having to issue DDL statements (create table, alter table etc) to > accommodate new data suggests that your db is not properly normalized. > After it's design, the db schema should (ideally) not change unless > your application requirements change. > > Check out these links: > http://www.troubleshooters.com/littstip/ltnorm.html > http://databases.about.com/od/specificproducts/a/normalization.htm > http://www.databasejournal.com/sqletc/article.php/1428511 > > Arnar > Wow!! This is a very database lesson. Thanks a lot Arnar. I'll start to redesign the database to reflect you suggestions. Few questions: 1) having one table for years, one for days, one for months, etc. the query to select one data isn't too difficult? (the answer should be: no with sqlalchemy ;-))) Because, my program, for each month, create a web page similar to a spreadsheet with the instruments like columns and the days of the month like rows. So, the planning administrator insert the users in correspondence with the instrument and the day. ---------------------------- | sept | av200 | av400 | etc. ---------------------------- |1 sun | | | ---------------------------- |2 mon | | | ---------------------------- | etc. | | | 2) the planning_sets table will go too big? 3) all relations are one-to-many, is it correct? -- ------------------------------------------------------------------- (o_ (o_ //\ Coltivate Linux che tanto Windows si pianta da solo. (/)_ V_/_ +------------------------------------------------------------------+ | ENRICO MORELLI | email: [EMAIL PROTECTED] | | * * * * | phone: +39 055 4574269 | | University of Florence | fax : +39 055 4574253 | | CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY | +------------------------------------------------------------------+ ------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642 _______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
