also, this system would have to take into account that the address might be edited on one invoice (requiring the creation of a new address record) without changing the address on all of the other invoices related to the same address record?
Seems like you should go back to the original set up then. Otherwise changes to the address will always show in all invoices that link to it.
Many businesses use a "copy" approach like this as they want to document the address and other client info that was current at the time the invoice was produced and then refer to the contact info in the client info when they need the up to the minute version of their contact information.
i think that is why i originally implemented that strategy, but as the invoiceing became more sophisticated/complex it grew to about 250 fields and i am trying to figure out the best way to trim that down to make it a little more manageable (hence using the address table instead of invoice address fields). apart from the complexity issue, is there any difference in overhead or performance for having the additional fields on the invoice table versus "duplicate" address records for each invoice? i was thinking i could flip the relationship and put the address id on the invoice (or invoices using that address) rather than having the address be the "child" record. but i wasn't sure if actually implementing (scripting) that might make the interface too cumbersome?
Selecting an address or client ID is definitely better than the reverse, but that means old invoices will display the current address for that client--which is something you said you didn't want to happen here.
Personally, I'd keep to fields in Invoices that look up values from a Contacts table when you select a contact record. The address fields make up something like 4 fields or so (Street, city, state, zip) so including them or removing them as a very small impact on the total number of fields (250) in your invoices table.
250 fields does sound like a lot of fields for an invoice table. Makes me curious as to what else you are doing with all of those fields here.
i think i didn't clearly explain the idea that i had in mind. typically, addresses are entered on a "contacts" based layout using a relationship where a portal displays addresses that have a contact id foreign key field. when an invoice is created and assigned to a contact, it looks up the current address of hte client and creates a new address record with identical address information (in the past it just imported this info into the invocie address fields). so now inthe current system the contacts current address can be changed or replaced without affecting the invoice. similarly, the invoice address can be edited or deleted without affecting the contacts addresses or the addresses for any other invoice as each invoice has it's own one-to-one address record linked by an invoice id foreign key field.
what i was thinking, was to have the foreign key field in invoices (address id) so that i could relate an unlimited number af invoice to a single address record. if a user then wanted to change the address for one of these invoices, i would have to implement a routine to search for the address they wanted to use and replace the address id field in the invoice to point ot a new address, or create a new address record if the address did not already exist. that way there would be no duplicate records, and no records would be accidentally modified. but, now that i write it all out, it does sound like a lot of work to get rid of a few fields.
one of the reasons why there are so many fields is that the system works with foreign currencies. this required essestially duplicating all of the usual fields (taxable subtotal, non-taxable subtotal, shipping, amount paid, etc, etc) so that there is a nominal amount field for each one and a local currecy field (in this case $US) for each one which is calculated based on the exchange rate at the time of the transaction. most of these fields are further triplicated in order to display the appropriate values with the appropriate currecy symbol on the customer's printed invoice. that probalby accounts for about 50 more fields than would otherwise be in the table. then there are about 50 various global fields that mostly are user preferences for things such as cutomizing invocie numbering and some temporary fields used in scripts for creating new invoices. most of this stuff i think i will relocate to a "preferences" table or something like that. but i would love to trim it down as much as possible... it takes way too long to match the import fields everytime i need to run an update!
...but the reason i am thinking about the address fields when it seems that there are much bigger fish to fry is that i started going through my edit process alphabetically and the address fields were the first things i stumbled upon that i thought i could remove!
OK, what you describe would work, but as you have found, it is complex to implement. It is also functionally identical to just keeping the original address fields in the invoice record and using a looked up value field option to copy over the current address info from the contacts table.
it would be functionaly identical, but in theory it could considerably reduce the amount of data i am storing for addresses (whether as fields in invoices or as fields in addresses) as most of my clients are repeat customers with an average of perhaps 20-30 invoices a piece (of course there are many non-invoice related addresses so it would not quite be a 20 to 1 savings... but still significant)... we'll see if i have the energy to implement. thanks for your thoughts and comments!
What is the benefit in this "savings"? Maybe you're saving a couple megs. What's the point?
well... it's a good question. i am not particularly knowledgeable about the subject, but i am just learning about "normalization". there are several levels of normalization and one aspect or criteria is that the database does not create or store duplicative records. so, clearly, storing 10 or 100 or 1,000,000 identical address records would violate this principle. in my case it is on the order of tens of duplicates for tens of clients, but for someone else using this database it could be many more. so while it is a little esoteric a consideration perhaps, it is the principle of designing the database to meet a certain standard or best practice. and while it may only be a couple of megs of difference at the moment, if i keep records for another 10 years, it may be 100 megs (which is still not very big) but this is just addresses. the idea would be to apply this principle to every aspect of the database (for all of the tables and fields). in addresses alone i may be achieving a factor of 10 reduction in the number of records i need to store. applied throughout the database, perhaps i can achieve similar efficiencies and hopefully, that efficiency will translate into performance gains. there is a wikipedia article on "normalization" which, although most of it is over my head, it pretty good at explaining the general concepts and lots of resources if you google normalization. but to answer your question for me... the point is to make the database as good as it can be. if there are 2 ways to do something, i would rather use the better one even if it is a little more work up front. similar to the principal of making scripts "portable"... while it is a little more work up front... it will almost always save you time and code later.
i should qualify all of the previous with: at least that is my understanding at the moment!
" the point is to make the database as good as it can be. "
Ah, well, my goals are different. "Make a database that accomplishes it's goals" is my perspective.
You're learning about normalizing data, and that good. That's an important skill to have when building a database. But I think you're worrying about it too much. I've been down that road and it's usually a waste of time and there's usually performance tradeoffs (you're always referencing related data so it gets slow). I feel "Practical normalization" should be the goal. Instead of doing this address thing, learn about custom functions or the the burgeoning FileMaker standards organization.
But if you do decide to give a shot, good luck and check out a podcast from this page. The one with Theo Gantos. It's a great discussion of normalization.
hey david.. those are very valid points. thanks for taking the time to respond and for providing the links... i am very interested in checking them out! i am somewhere between a novice and an intermediate in terms of my sills and i am working with a database that has been growing more or less organically for about 10 years. i am also always working at the limits of my abilities so when i first learn about something, i usually try to implement it in some fashion before i really know what i am doing or whether it is appropriate to be doing it at all... so as you might expect, there is a lot of undoing things a year or two later when i learn more... a little knowledge is a dangerous thing indeed! similarly, this whole normalization concept is new to me so i am trying to implement it in some way so that i understand it better. so maybe my answer about what the point was, was not entirely complete because part of the point for me (not just with this situation but in general) is to "learn by doing". based on your and phil's responses, it seems like this is probably not the best place to try and implement this type of normalization. thanks again for your help!