KeithG
AWF VIP
- Local time
- Yesterday, 17:48
- Joined
- Mar 23, 2006
- Messages
- 2,592
I have a db at work where a user will look up a company name and retrieve the CustomerNumber and PayCode (these two values change monthly) to input the numbers into our mainframe program. The users retrieve the company name from documents they are keying from so naturally different clients give different versions of a companies name. As a result the user will look up a company name and will get no results returned because the name is not formatted correctly. I inheritted this db and I am planning to redesign it. One more thing to note is that a single Company can have multiple CustomerNumbers and PayCode broke down by state. So to recap One company can have one Offical Name but many aliases. And One Company can have many CustumerNumbers and PayCodes. Below is what I have brianstormed and would like any input or suggestions. One thing I am concerned about is the State attribute. Some companies have a single custnumber and paycode for ever state, while others can have a custnumb and paycode for every state. What do I populate in the state field for companies with a single custnumb and paycode? You might be thinking I could use ALL meaning ever state but what happens if a company has a different custnumb for Wisconsin but every other state is tied to a single custnumb and paycode. Please let me know if you have any suggestions or see any flaws in my design.
tblCompany
CompID
OfficalName
Street
City
State
Zip
tblAlias
CompID
Alias
tblCompanyCodes
CompID
CustomerNumber
PayCode
State
tblCompany
CompID
OfficalName
Street
City
State
Zip
tblAlias
CompID
Alias
tblCompanyCodes
CompID
CustomerNumber
PayCode
State