advanced database design: table--> table details

[simpli]

Hi,

I have an issue, I've been turning it around in my head but I don't know whats the best way to handle it. Say for example I have an example where table A contains the following field with different values (this is simplified for sake of clarity).

field idAmountfrequencyrepeats

1100per month3 times

 

I have table B which I want to be a detailed version of table A so it would take the data of table A and spit something like that:

field IDmonthamount

1month 1100

1month 2100

1month 3100

 

So far so good. My problem is if the user decides that whats in table A is now 600 dollars for 5 months The data in table A can easily be updated to reflect that. It becomes:

field idAmountfrequencyrepeats

1600per month5 times

 

To reflect this however I cannot just update table B I have to delete everything pertaining to field ID=1 before i can add the following:

 

field IDmonthamount

1month 1600

1month 2600

1month 3600

1month 4600

1month 5600

 

I don't know if this is the best way to handle this and I want your experts' advice. with this current method my worry is the fact that we are going to need to delete data from table B whenever data from table A changes.

 

I also have the option to keep only table A and get my controllers (I'm using Zend Framework) to derive the equivalent of table B. My problem with that approach is it works for one record but since it doesnt create a table it doesnt allow me to retrieve data pertaining to several field IDs and to sum them by amount for instance.

 

I also have looked at using stored proc but I'm just not convinced if they are the proper solution to this problem? Can anyone give me some advice? I'd like to get a good design from the start to save myself later aggravation.

 

Thanks in advance,

JR

 

 

[synapp2]

JR

 

I find your example and descriptions to be - well - confusing. Sorry. That may be why you haven't gotten a response, thus far.

In the absence of a clear question, it's difficult to discern what you're trying to figure out, but I'll take a stab...

 

I gather that you're attempting to understand how to work with records that you want have a master-detail, or one-to-many relationship. Is that correct?

 

I'll assume yes, for the moment. Thinking out-loud, I'm wondering why the only ID value I see is: 1. I'm guessing, but I think perhaps you might do well to review some basic database design principles. Concepts like primary and foreign keys play into what it seems you're talking about and I don't really see that reflected in your examples.

 

Here's a link to more information: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

I hope this helps.

 

- Richard