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 <mjpelmear@xxxxxxxxx> > 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? > Category Registered Billed Total > electricity_use 120,00 150,12 102,36 > electricity_demand 130,00 150,12 102,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: > id category_name > 1 electricity_use > 2 electricity_demand > ... ... > (where id is the primary key) > > bill_data table: > ID_from_original_bill_table category_id registered billed total > 1 1 120,00 150,12 102,36 > 1 2 120,00 150,12 102,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<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 101 102 103 >> 104 >> >> *AND* >> >> Value Table >> >> *ID | Registered | Billed | Total* >> >> >> 101 120,00 150,12 102,36 >> 102 130,00 150,12 102,36 >> 103 150,00 150,12 102,36 >> 104 110,00 140,12 102,36 >> >> better? Thanks! >> >> >> 2012/9/19 Jim Giner <jim.giner@xxxxxxxxxxxxxxxxxx> >> >> 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 > >
