Posted by David Haynes on 11/02/05 05:45

meltedown wrote:
> 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
>
Well, I would actually do it more like:
table user: (says 'this user has this pay period')
user_id (primary key, auto incrementing)
period_type (enum('weekly', 'monthly'))

table invoice: (says 'this user has this invoice')
invoice_id (primary key, auto incrementing)
user_id (foreign key to user table)

table period_num: (says 'this period num is associated with this invoice')
period_num_id (primary key, auto incrementing)
period_num
invoice_id (foreign key to invoice table)

table time: (says 'this time record is associated with this invoice')
time_id (primary key, auto incrementing)
start_time
stop_time
invoice_id (foreign key to invoice table)

which makes it a bit more complicated, but helps with issues like
referential integrity.

One question that came to mind is, given the pay period and the user_id,
wouldn't it be possible to just compute the period num(s)?

-david-
caveat: I haven't done a diagram of this schema, so it may not be the
best possible. Just use it to see what I had in mind.

[Back to original message]