jcombs_31 Posted June 25, 2011 Share Posted June 25, 2011 I'm thinking maybe I'm going about this wrong, so here it goes. I have a table, lets call it actions, defined with only a couple fields (id, summary). Data would be like: 1 Update 2 Follow Up 3 Close .... I have a table with a number or fields that would reference actions. Ultimately this is for a review of sorts. I need to have about 10-15 fields reference the available 'actions' as defined in the actions table. Would I have to define each foreign key separately, or is there a better way to accomplish what I want. Table 2 has a number for foreign keys. field 1 => action field 2 => action field 3 => action So the constraints look like foreign key (field1) references actions(id) foreign key (field2) references actions(id) foreign key (field3) references actions(id) I thought this could be written as: foreign key(field1, field2, field2) references actions(id), but apparently I was sadly mistaken. I've been away from any type of dev for a while, so any thoughts would be appreciated. Quote Link to comment Share on other sites More sharing options...
ignace Posted June 26, 2011 Share Posted June 26, 2011 foreign key (field1) references actions(id) foreign key (field2) references actions(id) foreign key (field3) references actions(id) That is indeed correct. foreign key(field1, field2, field3) references actions(id, id2, id3) This would be used for compound keys. Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted June 26, 2011 Author Share Posted June 26, 2011 Ok, I for whatever reason just feel like I'm going about the design wrong, I guess because I never had to build a table like this. We use an internal system with a number of fields that we need to conduct reviews on. Each of these will be 'graded' with a follow up action. I guess this is the only real way to do it, just wanted to be sure. Thanks for the answer. Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 26, 2011 Share Posted June 26, 2011 could you post your tables and what are you trying to do exactly?.... a first glance and based only in the small piece of information that you posted the design seems very curious imho Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted June 27, 2011 Author Share Posted June 27, 2011 could you post your tables and what are you trying to do exactly?.... a first glance and based only in the small piece of information that you posted the design seems very curious imho What I'm really doing is creating an enum of sorts. I just need that enum to be flexible which is why I'm using another table to populate it. The extraneous data is someone irrelevant. Ultimately this is a relation between 2 tables. <table1> id action <table2> id field1 field2 field3 .... The values for table2 will be driven from the options available in table1. Quote Link to comment Share on other sites More sharing options...
ignace Posted June 27, 2011 Share Posted June 27, 2011 Why then not do something like: fields field_id field_name actions action_id action_name fields_actions field_id action_id Quote Link to comment Share on other sites More sharing options...
mikosiko Posted June 27, 2011 Share Posted June 27, 2011 ^^^ and that should be a better and more flexible design/solution Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted June 27, 2011 Author Share Posted June 27, 2011 Why then not do something like: fields field_id field_name actions action_id action_name fields_actions field_id action_id I think this is what I was looking for. Was having a mental block. Thanks ignace. I think though this leads to an additional table. Table 2 in my scenario also has additional data, for example notes, creation date, etc... Quote Link to comment Share on other sites More sharing options...
ignace Posted June 27, 2011 Share Posted June 27, 2011 jcombs you would help us to help you if you would post some more information than just those little pieces of information. Give us an ERD to look at and we can help you normalize it and/or provide advice/suggestions in how to improve the design. Quote Link to comment Share on other sites More sharing options...
jcombs_31 Posted June 27, 2011 Author Share Posted June 27, 2011 jcombs you would help us to help you if you would post some more information than just those little pieces of information. Give us an ERD to look at and we can help you normalize it and/or provide advice/suggestions in how to improve the design. I don't have an ER to post, but what I'm looking to is a review for a call center type system. This system has a number of fields that the support person must fill out (A CRM for Technical Support). There are a few tables that I need. [Employees] name, login, etc [Cases] Case number, details, etc [Review] Review Date Employee Case Number Case Summary <field in CRM> Problem Detail <field in CRM> Last Action <field in CRM> Notes Action An employee can own many cases and there can be multiple reviews done for each case over time. Within the review the CRM fields will be graded with a list of actions to choose from. This then leads to the actions table. This was the list can be flexible and added to as needed rather than using something like an enum. I like the concept of the many to many for fields <-> actions, but i'm struggling with the fact that a review will contain many fields from the CRM as well as additional data (Notes, Action Plan, etc). There are more details/tables that I don't feel are relevant to this particular relation. I thought about just handling this in PHP (just query the actions table to create the dropdowns for the input form and just use a varchar for the grading system in the table), but I didn't think this would be good design. Does this make sense what I'm trying to accomplish? 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.