0

I have a foreign key of "countyid" in multiple tables (52 to be exact) because 52 different objects in the software can be specific to a county (or it could be null, for all counties).

Without doing 52 "Unions", is there a way in MySQL to get the results in all 52 tables when I am looking for a specific countyid? (ie- looking up what I have in the DB for this particular county)

Or, to at least return the tables names which contain a record that has that certain countyid?

tadman
  • 208,517
  • 23
  • 234
  • 262
Charistine
  • 61
  • 1
  • 1
  • 7
  • you can do a massive search, but .... an id is usually an integer which should be on a lot more tables than just the ones you are looking for – Saikios Mar 21 '16 at 21:34
  • Sounds like your business logic compels you to join these things in as necessary, there's really no way around that. – tadman Mar 21 '16 at 21:39
  • 2
    Perfect use case for MongoDB :) There are a few things you can do. PHPMyAdmin has a search feature, spoken of here: http://stackoverflow.com/questions/562457/search-for-all-occurrences-of-a-string-in-a-mysql-database or, you can create that 52 table union and store it as a view for future reference, then just select out of that view like you had everything in one table. – JLB Mar 21 '16 at 21:41
  • Are you sure that there are 52 different kinds of thing? – Strawberry Mar 21 '16 at 22:07
  • There is no problem with doing 52 joins other than the typing of them, but just create a view for it once, and problem solved. No? – trincot Mar 21 '16 at 22:17
  • @Strawberry - Very sure. Forms can be for a particular county, Specific tasks can be for a certain county, Instructions on general tasks can be for a certain county, a Judge/Sheriff can only be in a specific county, etc. – Charistine Mar 22 '16 at 12:46
  • what if the system is rolled out to another county? What if a counties subdivide or amalgamate. I'm not convinced that your design is sensible- but hey, it's your design – Strawberry Mar 22 '16 at 13:33
  • @Strawberry It's not my design and thanks for your concern but I'm not trying to (or need to) convince you of anything. – Charistine Mar 30 '16 at 19:13

0 Answers0