Re: About Entity Relation Diagram

From: Rich R <rryan_at_cshore.com>
Date: Thu, 16 Dec 2004 23:07:05 GMT
Message-ID: <tyowd.47769$Qv5.31045_at_newssvr33.news.prodigy.com>

"Silver" <argytzak_at_med.auth.gr> wrote in message news:cprtnf$7dq$1_at_nic.grnet.gr...
> Hi,
>
> I 'm currently working on the ER diagram of a dating site.
>
> I have this question about the tables that will contain the members data.
>
> How can I organize the data so that the table remain as normalized as
> possible. In particular, I want my tables to be in the 3rd Normal form.
>
> For example, the table with the member's APPEARANCE: will I have to have a
> table with the member's ID and then the columns: eyeColor, hairColor,
> Weight, BodyType, etc... ? That makes the table too large. Is there any
> other smarter way ?
>
> What about the table with the member's PerfectPartner? Will I have to
> include the same attributes as in the APPEARANCE table? Again ?
>
> The same problem occurs with the table INTERESTS. I want to have a list of
> predefined interests that the member can choose from, as well as a
StringBox
> where one can enter something different. How can this be organized which
as
> much as redundancy as possible??
>
> I'm not asking to do this for me, just looking for advice on how to
proceed.
>
> Thanks!
> Silver
>

Normalization will not be the hard part. Tables will be something like this: CREATE TABLE Member(
MemberID int not null PRIMARY KEY,
MemberLastName varchar(35) not null
MemberEyeColor tinyint unsigned (from a reference table), MemberSeeking EyeColor ......,

.....
.....
...
...);

(Lots of interests for a member)
CREATE TABLE MemberInterests (
Memberid int not null,
InterestID (from an Interest reference table) PRIMARY KEY (MemberID,InterestID))

Your real problem will be writing queries such as:

Show me interests (and they must be members,too) with EyeColor is 'Blue" or EyeColor is 'Red" AND Interests include 'Canoeing', 'Camping', but not 'Square Dancing' (meaning they did not choose 'Square Dancing' as an interest, but 'Square Dancing' is not an interest. AND
Height BETWEEN(..... / ......)

Have a good night,

Rich Received on Fri Dec 17 2004 - 00:07:05 CET