On 9/6/06, Enrico Morelli <[EMAIL PROTECTED]> wrote: > Thanks a lot Arnar.
no problem.. > 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. one table for years, another for days and yet another for months would be over-normalizing :) Over-normalizing can degrade db performance/efficiency just as normalization can improve it. By the rules.. you should keep a table of days (with cols year, month, day) - but since this is an easily recreatable dataset, no table is really neccessary here. Given the tables above, a query to select the plan for a single month would be planned_days = session.query(PlannedDay).select_by(year=2006, month=9) this gives you a list of planned days - order them and in your table-building code, loop over they days of the month to create rows and if a corresponding day appears in that list, the relationship to Instrument will give you a list of instruments planned that day. Loop over all instruments to create columns and if a corresponding instrument appears in that list - that items should contain the cell data - otherwise, just output an empty cell. > 2) the planning_sets table will go too big? Not if you're using a good RDBMS. Even if you have 100 instruments, each planned every day, this gives you 36.500 rows pr. year - times 10 if your application runs for 10 years. Thats 3.650.00 rows - which MySQL and most others should handle easily. > 3) all relations are one-to-many, is it correct? Formally speaking, this is a many-to-many relationship between days and instruments - with an attribute on the relationship itself. >From SQL (and sqlalchemy) viewpoint, since there is no table for "days" - then yes this is a one-to-many relationship between the plan_set and instruments. Arnar ------------------------------------------------------------------------- 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
