A location is a code varchar(10) and currently consists of 6 numeric digits where the first three are always zeros.
First, leading zeros tend to be for display and I probably wouldn't store the location ID's that way. I'd make the location either an INT, or possibly, because of the limited number of possibilities, a SMALLINT. I'd add a calculated column to the location table to display the SMALLINT value with the required leading zeros. It'll save on some storage space and possibly improve performance, a bit.
As for the Executive and Contact tables...
I'm not sure that I would separate these... I believe I'd put Executives in the Contact table with a flag that indicated they were executives. In either case, I'm not real sure how you would handle a Contact being promoted to an Executive status. I'd have to think about that.
I'm a bit confused about ModelID and SourceID in the Contact table. It would appear that each contact is somehow responsible for a given Model or set of Model's from different sources. That's quite a bit of denormalization in that if, say, a given Contact was female and changed her name at the marriage event, you would have to change that name on more than one row. I believe that a cross-reference table between Contacts and Models would suite better. It does add some complexity as 3rd normal form has the tendency to do, but I believe it will be better in the long run in so far as maintainability goes.
Also, if Models can come from different sources, I'd add the SourceID to the Model table and include that in the cross-reference table I spoke of... that would be a source of great relief for the normalization of the Contact table.
Once that were accomplished, then I believe there would be no need for separate tables or databases for each Location. Simply add the LocationID to most of the tables. Properly indexed, I don't believe performance or scalability will even come close to being an issue.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs