shanetastic Posted September 21, 2011 Share Posted September 21, 2011 I'm trying to get everything mapped out before I start writing my php code. I have three factors that I plan on storing in databases: company, rep, state. Only one rep can work with a given company in a given state. A rep can represent the same company in multiple states. For example: RepA could represent CompanyA in NC and SC. RepA could represent CompanyB in NC only. And RepB could represent CompanyB in SC only. My thought is that the best way to do this is with three types of tables: -table 1 is a list of reps -table 2 is a list of companies -then I would have a table for each state. These tables would show the relationships of reps to companies in that state. So my example would look like this: Rep Table: rep RepA RepB Company Table company CompanyA CompanyB NC Table rep company RepA CompanyA RepA CompanyB SC Table rep company RepA CompanyA RepB CompanyB Am I on the right track or is there a better way to do this? Do I need to add indexes or anything like that (sorry, I'm completely new to mysql). Quote Link to comment Share on other sites More sharing options...
gizmola Posted September 21, 2011 Share Posted September 21, 2011 No, you never want to make seperate tables for something like this. You should have a state table with 1 row per state. Have a companyRep table that relates Reps to companies. I'm going to assume that Company And Rep each has an integer primary key auto_increment on it, named "{table}_id". Your CompanyRep table would be: CompanyRep -------------------------- company_id (pk) rep_id (pk) Now Have an additional table named CompanyRepState that simply indicates the states that a particular "CompanyRep" can cover. CompanyRepState ----------------------- company_id (PK) rep_id (PK) state_code (PK) For the CompanyRepState table, you will add a unique index on company_id, state_code. This index will only allow one row to be in the table for any company/state combination and will enforce your business rule. Quote Link to comment Share on other sites More sharing options...
shanetastic Posted September 21, 2011 Author Share Posted September 21, 2011 Thanks. This is very helpful, but I hope you can add some more clarity for me. If I understand you correctly, you recommend doing this with a total of five tables: one that lists company names and company ids, one that list rep names and rep ids, one that list state names and state ids/abbreviations, the CompanyRep table that you describe and the CompanyRepState table that you describe. Is that correct? What is the need for the CompanyRep table since that same information is also going to be contained in the CompanyRepState table? Taking that a little further (and I know this is a noob question), what not have only the CompanyRepState table and populate it with the actual names instead of ids linked to other tables? For example: CompanyRepState RepName CompanyName State ----------- ---------------- ------ RepA CompanyA NC RepA CompanyA SC RepA CompanyB NC RepB CopmanyB SC Quote Link to comment Share on other sites More sharing options...
shanetastic Posted September 21, 2011 Author Share Posted September 21, 2011 I believe I have found the answer to my last question (the one I referred to as a noob question). Here is a great article about design mistakes: http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/. The author spends some time explaining "normalization", which answers my question. My first question, still stands though: What is the need for the CompanyRep table? Thanks again for taking the time to help a rookie out. Quote Link to comment Share on other sites More sharing options...
shanetastic Posted September 21, 2011 Author Share Posted September 21, 2011 I think that I am going to move forward with Gizmola's recommendation, minus the CompanyRep table (unless I figure out if it is needed). So here is what my final product will look like Rep ----------- RepID (PK) RepName RepURL Company ----------- CompanyID (PK) CompanyName CompanyURL CompanyDescription States ----------- StateID (PK) StateAbbreviation StateName Territory ---------- RepID (PK) CompanyID (PK)* SateID (PK)* *these two columns will be made into a unique index. If anyone has any feedback please let me know. Otherwise, thanks again to Gizmola! Quote Link to comment Share on other sites More sharing options...
gizmola Posted September 21, 2011 Share Posted September 21, 2011 I'm glad you honed in on the question of the CompanyRep parent table. I agree that it is optional, but I didn't want to get into a long thread about alternative designs. Depending on your application and how the keys are setup it could be beneficial or not depending on the flow of your application. For example, one thing it allows is an intermediate state where the application has identified that a person is a company rep, but you don't yet know what if any states they are representing. All things considered, I think your design is solid for this application. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.