|
|
Posted by meltedown on 11/02/05 05:09
David Haynes wrote:
> meltedown wrote:
>
>> I hope this makes sense.
>>
>> I have a script which stores login and logout times. When the user
>> creates an invoice, a function groups the times in weeks or months
>> (according to the user preference for the payperiod), assigns each week
>> or month a number, and then shows the chosen week or month.
>>
>> I can create and store invoice data simply by storing the user's
>> payperiod ('week' or 'month') at the time the invoice is created, along
>> with the periodnum so I get the proper week or month.
>>
>> So I have an invoice table that looks like this:
>> invoiceid
>> userid
>> payperiod
>> periodnum
>>
>>
>> So far so good. The problem comes when I want to store more than one
>> sequence number in one invoice.
>>
>> I could store the periodnums in a format like this :
>> "11_12" or "22_23_24"
>>
>> But this seems like a bit of a hack.
>>
>> Or I could have another table just for periodnums:
>> invoiceid
>> periodnum
>>
>> and then insert a new row into that table for each periodnum I add to an
>> invoice.
>>
>> Is there a better way ?
>> How would you do it ?
>
>
> From your description this sounds like a standard one-to-many
> relationship. i.e. one invoice may have many (actually one or more
> period nums). The way this is usually modeled is to have a separate
> table for the period nums with a reference to the associated invoiceid
> in each record.
>
> The query for the period nums is then based upon a join between the
> invoice table and the period num table using the invoiceid as the join key.
>
> -david-
>
Thanks for your response. Thats about what I thought, but I'm not used
to tables with only 2 fields, like this:
invoices table:
invoiceid
userid
periodnums table:
invoiceid
periodnum
payperiod
Navigation:
[Reply to this message]
|