Database and permalink structure for a directory site
I started building a business directory from scratch with PHP & MySQL but before I go further with the design, I want to finalize the database and permalink structure so that I will not need to make dramatic changes later.
The directory will have the following:
- pages (e.g., About, Contact)
- posts (blog posts for directory news & updates)
- listings ( business listings with all details like name, address, phone, city, state, country, website etc.)
- locations (city, state, country)
I am considering using a page.php, post.php, listing.php and location.php file for different styles and content.
On one side, I want an optimum database structure and on the other side, I want a user friendly permalink structure. For example:
I can have pages and posts in one table,
I can have business listings in one table,
I can have a table for locations as well (city, state, country).
Question 1: How would you design the database for such a directory?
The real problem is about the permalinks. Because I need to design the database considering the permalinks. For example, I want the following type of permalinks:
Question 2: I know basic redirection with .htaccess and I can do that if it was one type of content such as pages, but it becomes complicated when there are different type of database entries. How would you handle the permalinks?
I have been trying to find tutorials for creating such a business directory but couldn't find anything. Any ideas, comments, suggestions or tutorial links would be appreciated.