Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
We are getting closer. But I'll give some image to better ilustrate: http://177.71.185.219/stable/anexos/505b18fc713f8imagem.png this is my table in Flex, the fields are in portuguese so 'ignore' it. 2012/9/19 Matt Pelmear mjpelm...@gmail.com Bruno, This schema layout seems a little odd to me. Maybe I'm just misunderstanding the use. Correct me if I'm wrong here: Each row in the bill table is a customer? (or some other lookup value for which you're generating the data grid?) I assume the 15 fields from your original message are the 20 fields you are describing here? The values inside the Bill table are 100% unique and point to the Value table? You're trying to generate output that looks like this? CategoryRegisteredBilledTotal electricity_use 120,00 150,12102,36 electricity_demand130,00 150,12102,36 ... for a particular row in the Bill table where ID=something ? If this is true, I would consider normalizing this a bit more. If you break the columns in your bill table out into their own table, expand your values table a bit, you could end up with something like this: categories table: idcategory_name 1 electricity_use 2 electricity_demand ...... (where id is the primary key) bill_data table: ID_from_original_bill_tablecategory_idregistered billedtotal 1 1 120,00 150,12102,36 1 2 120,00 150,12102,36 ... (where (ID_from_original_bill_table, category_id) is the primary key) And do queries like this: SELECT c.category_name, bd.registered, bd.billed, bd.total FROM categories AS c JOIN bill_data AS bd ON c.id=bd.category_id WHERE bill_data.ID_from_original_**bill_table=whatever; Or am I still not understanding correctly what you're trying to do? If you actually need to do a pivot you might want to look here: http://www.artfulsoftware.com/**infotree/queries.php#78http://www.artfulsoftware.com/infotree/queries.php#78 -Matt On 09/19/2012 01:33 PM, Bruno Sandivilli wrote: Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
I'm a little confused here. You have a 15x3x3 set of data to display in a 3 column, 15 row datagrid? Are you displaying a single value in each cell of the datagrid, or all 3 values for the cell? How many variables are needed to uniquely select a piece of data from that 3-dimensional space? Can you provide a little more detail about the data? -Matt On 09/19/2012 11:44 AM, Bruno Sandivilli wrote: Anyone? Please. 2012/9/18 Bruno Sandivilli bruno.sandivi...@gmail.com Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Re: Many columns (as FK) vs 3x more Columns (in the same table)
Bruno, This schema layout seems a little odd to me. Maybe I'm just misunderstanding the use. Correct me if I'm wrong here: Each row in the bill table is a customer? (or some other lookup value for which you're generating the data grid?) I assume the 15 fields from your original message are the 20 fields you are describing here? The values inside the Bill table are 100% unique and point to the Value table? You're trying to generate output that looks like this? CategoryRegisteredBilledTotal electricity_use 120,00 150,12102,36 electricity_demand130,00 150,12102,36 ... for a particular row in the Bill table where ID=something ? If this is true, I would consider normalizing this a bit more. If you break the columns in your bill table out into their own table, expand your values table a bit, you could end up with something like this: categories table: idcategory_name 1 electricity_use 2 electricity_demand ...... (where id is the primary key) bill_data table: ID_from_original_bill_tablecategory_idregistered billedtotal 1 1 120,00 150,12102,36 1 2 120,00 150,12102,36 ... (where (ID_from_original_bill_table, category_id) is the primary key) And do queries like this: SELECT c.category_name, bd.registered, bd.billed, bd.total FROM categories AS c JOIN bill_data AS bd ON c.id=bd.category_id WHERE bill_data.ID_from_original_bill_table=whatever; Or am I still not understanding correctly what you're trying to do? If you actually need to do a pivot you might want to look here: http://www.artfulsoftware.com/infotree/queries.php#78 -Matt On 09/19/2012 01:33 PM, Bruno Sandivilli wrote: Sorry for the lack of explanation. Again: I have to model a bill table. The bill have 20 fields. But for each field we have: *registered* value, *billed *value and* total value.* So i've created an *bill* table with *20 *fields (representing the bill fields, like: consumption, demand, etc ), and a *bill_values* table (with *id*,*registered*,*billed *and *total*). I'll try to ilustrate: Bill Table *ID | eletricity_use | eletricity_demand_ | eletricity_traffic_use | eletricity_penalties *| etc 1 101102103 104 *AND* Value Table *ID | Registered | Billed | Total* 101 120,00 150,12102,36 102 130,00 150,12102,36 103 150,00 150,12102,36 104 110,00 140,12102,36 better? Thanks! 2012/9/19 Jim Giner jim.gi...@albanyhandball.com On 9/19/2012 3:12 PM, Jim Giner wrote: On 9/18/2012 8:52 AM, Bruno Sandivilli wrote: Hi, i strugling to decide what is the best choice: I have a 15 row x 3 columns Flash DataGrid, it means, for each row i have 3 values. To represent this in my Database, I could: 1. Create 2 Tables : A Values table - with 3 columns ; and a Bill table (with 15 foreign keys, each one pointing to one row in the Values table). 2. Create one Table with 45 columns (15 fields * 3 values for each field). I want to know, wich is the best choice? To manage my query, now i have a SELECT with a thousand of leftJoins. This is the best choice? How could I run a query wich will give all results linked, like: ( column_1_val_1, column_1_val_2, column_1_val_2, column_2_val_1, column_1_val_2, column_1_val_3, etc...) ? Thanks! I don't get it either. You have 15 rows with 3 cols each. So? Display them. Query them. Big deal. What is the real difficulty here? I'm not seeing it. (I don't read it as 15x3x3 - at least that's not what he said since he said 'for each row he has 3 values') Continuing on - why is your visual structure at this point NOT the same as your physical structure? I don't think you're telling us what your real problem is here. We need more information. You have '15 row with 3 cols each', ie, 'each row has 3 values'. So - your table has 15 records in it, each with 3 columns. Add a key field to give each row an identity and that's it. No? Sorry - but again - this post is not showing me a clear problem. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
