Re: 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?

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 -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@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