I am building a hunting outfitter website where I will have a list of outfitters, states and countries and a list of species to be hunted I want people to be able to search by state, country or species. For example, when they click on species, I want a list of outfitters to appear however, I want them to then have the option to filter by state or country. Same goes for when they click a state/country, I want them to be able to filter by species. Keeping in mind that one outfitter usually offers services for more than one species. I have already built my customer database with member info and login tables. How many databases should I organize the above information? Are the filters just going to be PHP code?
Hmmmm.....I don't think there is any need to put the information in different databases. Different tables maybe, different databases no. I suppose it all depends on how much data your storing. If its a few hundred records then one table would suffice. You'd have a table with the columns Outfitter_ID,Outfitter,State, country, Species 1, species 2, species 3.... I'm assuming that your search function will enter the strings into an SQL query. The filters you talk about would simply be WHERE clauses in the SQL statement. So when your customers entered their requirements on your website they are simply adding more where clauses. If it's thousands of records, you might want to start thinking of splitting the data up. You'd have Outfitter_ID, Outfitter, State, country in one table and the Outfitter_ID, Species1, Species2, Species 3 in another. Essentially if you've got a lot of data you are storing, you don't want massively 'wide' tables as these take longer to query than 'narrow' tables. For example if your customer just wanted to search on state or country, only the first table would be queried. The second would only be touched when they wanted to narrow down their search and decided they only wanted to hunt ducks- in which case both tables would be queried. I hope I'm getting the right end of the stick. If not try and narrow down the bits you are struggling on and I'll try and help out. Calix
Thats a great start and thank you very much. I will build the rest of the table(s) and check back if I have more questions.
One more thing.....I like the idea of the outfitter_id however, members will allso have a member_id as the primary key. How does that change things?
Each table has a primary key, rather than each database. You can have multiple tables within a database with different primary keys. The primary key is just used to uniquely identify something, in this case an individual customer and an individual outfitter. So you'll have your customer data stored in one table with customer_id as the primary key. Then in your outfitter details table you would have outfitter_id as the primary key.