Multiple one to many joins between two tables?

[andypooz]

I would like to ask a question about organising my database. I have set up my tables in a certain way, but it could be better done another way.

The database will drive a dating-type site. A 'seeker' will create a profile of what they are looking for in a partner(height, interests, eye colour, etc) and in each case, nominate the strength of the preference (1- Not acceptable,.. 3- OK,.. 5- Essential) for each characteristic. As far as I can tell there are at least two ways of going about it:

TABLE: SEEKING
profile_id
seeker_id (the person setting up the profile)
max_height
min_height
height_pref_strength
eye_ colour
eyecolour_pref_strength
...and the table goes on with each possible characteristic and its pref strength

then...
TABLE: PREF STRENGTH
pref_strength_id
pref_strength_description

Now here is my problem: If I list all of the possible characteristics a person may nominate a preference for in one record of the 'seeking' table, then:

1. I will need to either create a table each for eye colour, hair colour, build, religion, etc to provide the id and description OR I could create a 'characteristic_details' table with all id and descriptions and a characteristic_type_id to separate hair colours, eye colours, body types (which would then only necessitate creating one other table to describe the different characteristic_type_details)
2. It would mean that there would be multiple one-to-many joins between the seeking table and the pref_strength table (as each characteristic listed in a record in the seeking table would have an associated preference strength id)

a way around this?

TABLE: SEEKING_CHARACTERISTICS
profile_char_id (pk)
profile_id
characteristic_id
pref_strength

Then each profile would have many seeking_characteristics records linking to one profile, and only 1x one to many relationship between the seeking_characteristics and the pref_strength tables. It would also mean there'd be less empty field if people didn't really have a preference for eye colour or anything else.

I seem to have solved this as I type it out, but I'll go on...

My other problem is that characteristics like height will have two fields for min and max height (as well as pref strength). These would just be numbers like 168 and 192 representing cms, so no record in the characteristic_details table would be necessary. Should I just put these special (dual-field, only numbers) characteristic fields in the main profile table and devolve all the others to the seeking_characteristics table? This would mean multiple one-to-many relationships between 'seeking' and 'pref_strength' again!!

So here goes...

TABLE: SEEKING
profile_id (pk)
seeker_id (fk)
max_height
min_height
height_pref_strength (fk)
max_weight
min_weight
weight_pref_strength (fk- second to same table)


TABLE: PREF_STRENGTH
pref_strength_id (pk)
pref_strength_description

TABLE: SEEKING_CHARACTERISTICS
profile_char_id (pk)
profile_id (fk)
characteristic_id (fk)
pref_strength

TABLE: CHARACTERISTIC_DETAILS
characteristic_id (pk)
characteristic_description
characteristic_type (fk)

TABLE: CHARACTERISTIC_TYPE
chartype_id (pk)
chartype_description

As far as I can tell that's the best solution but I'm scared about the complexity of the queries I'll have to design when it comes time to match up an individual's profile (which will use the same characteristic_id references as the seeking profiles) with a 'seeking' profile ! What will the performance implications be? Is this equivalent to me saying "I've just learned to count to five, now how do I find the cube root of 1176?"?

Suggestions and your expertise VERY much appreciated.

Also, if there are any open-source dating site scripts you know of I could have a look at the way they organise their database (or even use theirs and adapt to my own purposes!) Yeah, thats a good idea, thank for listening to my rant, its given me loads of ideas in itself!

For those of you worried that I'm using Access for a website, don't fret. I will convert it to MySQL later using a conversion programme, but I like the visual tools of access that allow my to draw the relationships so that I know what I'm doing. MySQL doesn't seem to let you do that. Not phpmyadmin or navicat or anything I've found. I know that I'll have to go through an change alot of the data types once its sql, but at this stage while I'm still trying to get my head around the structure of the monster (at the moment 26 tables, and only about half done!), MySQL is not an option for me.

Very long post, sorry. Your thoughts good people...

 

[MarkK]

I think your charateristics table needs to be independent of any strenth of preference data. Strength of preference is a property of the relationship between a seeker and the and a characteristic, not a characteristic alone. Here are some quick, and incomplete, thoughts about the tables

tCharacteristic
CharacteristicID (PK)
CategoryID (FK)
Description
ShortDescription

tCharacteristicOption
CharacteristicOptionID (PK)
CharacteristicID (FK)
Description
Value

tSeeker
SeekerID
Name
SignUpDate
etc...

tSearch
SearchID
SeekerID
SearchName
DateCreated

tSearchDetail
SearchDetailID
SearchID
CharacteristicOptionID
StrengthOfPreference

So using this structure a seeker creates a search object. The search object options are generated from the existing characteristics you have so it's easy to change your characteristics list. Also, the search object only contains search details that the user has selected so you don't process a pile of empty fields. Strength of preference, as you can see, is way at the bottom of the pile. Strength of preference is an attribute of an detail of a search created by a seeker.

Note also that there should not be a field called eye_color. Eye Color is data, not structure.

Cheers,