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 <[email protected]>
>
>> 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