May 1, 2007 at 7:58 pm
I'm working on re-writing my companies Front and Back end processes and just wanted some input from others on a Storage Mechanism.
I need to store the following:
CustomerID (Identity field)
Phone Number / Title / First Name / Last Name / Address
The main goal we have is to allow for Multiple customers at the same phone number (although each will have their own customer ID field so they'll all be unique records. IE Husband and Wife at same phone number, but each have different additional attributes).
My thought process was to do the following:
Create a table of CustomerID / PhoneID, TitleID,FirstName,LastName,AddressID.
Create a table of PhoneID (linked by CustomerID in a one to many relationship).
Create a table of TitleID (linked by TitleID with a one to many relationship).
Create a table of AddressID (linked by AddressID with a one to many relationship).
Any thoughts on whether this is the atypical way to go about storing this information or do you guys have other thoughts / ideas?
The amount of records going into this database will be ~10 Million by the time I bloat my current set of Customers (and before I archive probably half to three quarter of the total count). Obviously storage space isn't the issue, instead it's more of a best practices approach and making sure I'm going about things in the most efficient manner possible. I don't want to waste space / CPU cycles reading through redundant and useless information that might be easier / quicker to just handle via a relationship.
May 2, 2007 at 9:29 am
We've done something like this in the past and it gets cumbersome, but it accomplishes what you want. The trick is the breaking/merging of phone numbers. Sometimes you'll have two or 10 people with the same phone, but then some get new numbers. You need to be able in the app to easy break these people off into a new number and not just update the same number for other people.
Same if they want to merge.
Honestly I don't think you save enough space to use a separate table for phone. A phone is 10 digits, US, which is 10 bytes. The 2 FKs (int) are 8 bytes. So 2 bytes x 10M isn't enough to worry about.
Instead I'd store phone with the customer and either update all at once if needed, or retrieve the same # for a new customer if they're linked (figure out the linkage between customers, which I don't see), or just enter a new one.
For addresses, I'd leave them separate, but same merge/split issues.
HOWEVER, keep in mind that you need to handle multiple types for each (home, work, cell, etc.) and then maybe a separate table makes sense.
Look at how often you need to pull all this info back in the app as well. If you need to join these tables a lot, then you might just limit the types and stick them in one table. Or two. Depends on the volume of queries.
Hope this helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply