Forgive me as I am still learning about solid database design techniques. I have a search application I am working on with a client. The search parameters are many and many of the characteristics of the tables can be broken down into many possible options.
A one to many relationship, basically.
The thing is, I know it is good practice to take any multivalued fields like the ones I am facing and split them off into separate tables that all share a key with the primary table.
My problem lies in the fact that at least 13 of my fields could be broken down into separate tables ending up with over 14 tables to hold all of my searchable data.
Is that too many? Or are there ever too many?
I planned to perform the searches on a large View which of course incorporated the pertinent data from all of the other tables.
too many is reached when the database server tells you "cannot execute query because too many tables"
multivalued fields can be searched, but this will never utilize an index, it will always require a table scan, so if you want your queries to slow down as the number of rows increases (i.e. your app will not scale) then go ahead and use multivalued fields