Create a table called states.
Create a table called cities that as a many to one relation ship for a table called states.
Then in your post table you have a many to one to the cities tables.
Then you can use syntax like post.city.state.name to get the name of the state for the post or just post.city.name for the city.
How would I guarantee to the user that no matter how he types "chair"
(singular, plural, with or without adjectives), he will get all the
results that the database can provide?
Wildcard queries could solve your immediate problem. For example, your products table may have the following entries:
productID companyID productType
--------------------------------------
1 1 chair
2 2 wooden chair
3 2 desk chairs
If you wanted to get all of the products with the word chair in it, you could run the following query (mysql):
select productID, productType
from products
where productType like '%chair%'
However, that design isn't optimal, because you will encounter a problem when a user types in recliner, or some other type of chair that doesn't explicitly have the word "chair" in it.
A better design would be to have a separate table that specifies the categories of products. For example, you could have a categories table that contained a fixed set of categories:
categories table
categoryID categoryName
---------------------------
1 chair
2 office furniture
3 wooden furniture
Then, you would have a table to map categories to products. Products can have many categories, which will give your users more flexibility when searching. Here is the productCategories table.
productCategories table
categoryID productID
-----------------------
1 1
1 2
1 3
1 4
2 3
3 2
Finally, you would have your normal products table, which would contain additional information about your products.
Products table
productID companyID productName price
---------------------------------------------------------------
1 2 Plain chair 40.00
2 2 Modern wood chair 120.00
3 2 Black office chair 160.00
4 3 Leather recliner 800.00
With this model, finding all of the chairs is simple. You just need find all products assigned to the chair category.
select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName = 'chair'
This would return the recliner as well. Also, this model gives the user some flexibility for creating more specific searches. For example we can change only one line (see the last line of the query below) to fetch all of the wooden chairs.
select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName in ('chair', 'wooden furniture')
Best Answer
You need three tables -
User,Interestand an associative table for the many-to-many relation between them (aUsercan have manyInterests and anInterestwill have manyUsers), call itUserInterestLinkor something.UserandInterestwill have autoincrementintfields as a surrogate primary key,UserInterestLinkwill have FKs to bothUserandInterest, like so:I didn't mention the PK on
UserInterestLink, for efficiency it would be a compound PK on(UserInterestLink.InterestID, UserInterestLink.UserID), but there's also an argument to be made for a seperate surrogate primary key (which I personally think is a waste of space, unless you need to allow for multipleUserInterestLinks between oneUserand oneInterest). For a purely associative table in a many-to-many relationship, though, the compound primary key should work fine.Edit: The complicated part of this isn't in the relational design, it'll be in the application code, because you'll need best-match/partial matching in order to show users the groups that most closely match their interests (rather than having accidental splinter groups all over the place). And if you want to out-Facebook Facebook, you'll need a smoother and better user experience than they offer.