MySQL Forums :: Database Design & Data Modelling :: Theoretical Database Table Structure

Advanced Search

Theoretical Database Table Structure
Posted by: Bat Man ()
Date: June 04, 2012 10:53AM

Hello, I have a question in regards to the most optimal way to design a database.

I do not have a database setup at this time, I am just an extremely curious individual, and will be creating a new site in the near future where any ideas I get from this thread will be applied directly into that project.

Let's, for example, use an online dating site (as I feel the structure best suits my questions/concerns) as the concept. A website dealing in cars could also be used, I guess.

Let's assume there is an this website allows a user to search other users based on many characteristics: eye colour, height, sex, race, smoker, non-smoker, drinker, non-drinker, social drinker, heavy drinker, and so on. The list could go on and on.

Now, the refinement queries, as is pretty obvious, can get pretty long as the user keeps checking off refinements during their search.

Would it be best to include all refinements within a single record for each user, or create multiple tables for each refinement?

For example, take a single record like such that might have the following columns:

id, username, first_name, last_name, email_address, eye_colour, height, sex, race, religion, hair_colour, weight, drinker, smoker, age, hobbies_and_interests, education, style, etc.

Or, break them all into tables. For example, give height its own table (user_id, height), eye_colour its own table (user_id, eye_colour), etc.

Would it quickest to have each refinement in its own table with a `user_id` key to make the relationship between the `user` table and each refinement table? That way, each query would be reading off of indexed keys. I worry that the initial method (single record) might be extremely difficult to index and once refinement queries start getting down to ~8+ refinements, the queries could start slowing down significantly (if not after only a couple). There is just no way to know what collection of refinements a user will enter in as they're ALL optional.

And this is only a handful of refinements. Let's assume this could get up to ~30 refinement options as the method must be scalable.

I have just never been able to track down any ~real~ good documentation that answers my intrigue effectively. I hope somebody can shed some light onto this.

Thank you in advance for your time.



Options: ReplyQuote

Subject Written By Posted
Theoretical Database Table Structure Bat Man 06/04/2012 10:53AM
Re: Theoretical Database Table Structure Peter Brawley 06/09/2012 08:50AM
Re: Theoretical Database Table Structure Rick James 06/12/2012 06:26PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.