RE: Foreign Keys

[Ben A. Hilleli]

So you are talking about parent-child relationships in a single table, or
in the technical jargon 

reflexive relationships. See www.artfulsoftware.com
http://www.artfulsoftware.com/  for detailed examples of how this is done,


but here is the thumbnail sketch:

--

Actually a Parent-childREN relationship. 

A USER can have 0 to many friends. 

This person can also BE THE friend of 0 to many other USERS.

 

AKA: I (A user) can have many friends (other users), I can be the friend
(user) of many users



The table has to have a ParentID column (call it what you want) that
points, in the case of a 

Friend, to the Friend Of Whom UserID column

--

Not sure what you mean as aren't you describing a 1 to 1 relation?

 

I'll check the site mentioned, thank-you so much!

 


Ben A. Hilleli

Programmer / Analyst






 

  _  

From: Arthur Fuller [mailto:[EMAIL PROTECTED] 
Sent: October 8, 2008 2:55 PM
To: Ben A.H.
Cc: mysql@lists.mysql.com
Subject: Re: Foreign Keys

 

So you are talking about parent-child relationships in a single table, or in
the technical jargon reflexive relationships. See www.artfulsoftware.com
http://www.artfulsoftware.com/  for detailed examples of how this is done,
but here is the thumbnail sketch:

 

The table has to have a ParentID column (call it what you want) that points,
in the case of a Friend, to the Friend Of Whom UserID column. 

 

This simple statement avoids the complexity of the situation where a Friend
can have Friends and so on. That problem requires detailed explanation,
which you can obtain from the site mentioned above. See also Joe Celko's
books.

 

Arthur

On Wed, Oct 8, 2008 at 3:33 PM, Ben A.H. [EMAIL PROTECTED] wrote:

I know that this is the standard means of dealing with a  many to many
relationship, I'm just not sure it fits here.



 USER:

emailID (PK)

userName

Password

Address

Etc



 FRIEND:

emailID (PK)



 USER_FRIEND

user_emailID (PK)

friend_emailID (PK)



So if I want a list of USER [EMAIL PROTECTED]'s friends:

SELECT friend_emailID from USER_FRIEND

WHERE user_emailID=[EMAIL PROTECTED]



In this (and pretty much every case), the FRIEND table is useless and
doesn't make sense logically.



*I THINK I EXPLAINED THINGS INCORRECTLY*

Let me try again:

* I'm not sure if it's even a true many to many relationship as this is
actually a relationship between ONE RECORD in a TABLE and a ANOTHER RECORD
in THE SAME TABLE!



A USER is:



USER

 emailID

 userName



A friend is really just another RECORD in the USER table.



i.e. IF I HAD TO MAP THE RELATIONSHIP LOGICALLY, IT WOULD BE:



USER:

 emailID

 userName

|1

|many

FRIEND:

 emailID

|many

|1

USER:

 emailID

 username



(i.e. it's two records in the same USER table)



How are relationships between records in the same table usually dealt with
in terms of design? Implementation?



ThanX,



Ben

Jim Lyons [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]

 Indexes speed up joins.  Foreign keys should be indexes themselves, so
 they
 can also speed up joins.  If the FK is not an index, it won't help.  So,
 index your FKs

 On Wed, Oct 8, 2008 at 10:43 AM, Ben A.H. [EMAIL PROTECTED] wrote:

 Does using foreign keys simply enforce referential integrity OR can it
 also
 speed up JOIN queries?



 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/[EMAIL PROTECTED]




 --
 Jim Lyons
 Web developer / Database administrator
 http://www.weblyons.com http://www.weblyons.com/ 





--

MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

 

Normalization / Foreign Key Question

[Ben A. Hilleli]

Hello,

 

A bit embarrassed, I have been away from the databasing side of system
development for quite a while so am a little rusty. This seems like a simple
issue.

 

I have a USER table:

 

userID

fName

lName

address

address2

city

province

country

referrerID

 

userID is the unique primary key.

 

My question pertains to referrerID. The referrerID specifies which OTHER
USER referred this new user. Thus it is a foreign key that links to a userID
in another user record. I do not recall ever linking attributes from the
same table (even for different records) so I have a hunch that the way to do
this properly is either:

 

(A)

 

User UserReferrer

-  ---

userID (the new userID)---userID 

 referrerID (the userID of
the referrer)

 

or (B)

just store referrerID as a number and use a simple query to get the info for
the referrer when it is required.

 

Select name from USER

Where userID=currentUsersID

 

 

Which is the correct way of doing this? If both, which would be ideal, this
will be a relatively large database with approx 250,000+ users, not all will
have referrerIDs, I would wager maybe 1-10% of these people will have been
referred. Priorities for us are simplicity/scalability, efficiency (in that
order) 

 

   Ben A. Hilleli

   Programmer / Analyst

 

 

Multi-Language Web Content

[Ben A. Hilleli]

Hi,

 

I am in the planning stages of a multi-language web-content  user
management system.

 

I may not be here to support this app forever so priority is on
maintainability, scalability (pref. by less experienced programmers or
laymen)

 

We have a bunch of questionnaire type pages (name, address, sex, tv watching
habits, salary etc.) that are populated dynamically based on database and
xml (more below)

Users fill out these pages, and that information is stored

 

I want to store the information for these pages (i.e. the available options
for salary, the question-text for the salary question) in a mysql database

BUT this site is multilingual so options etc. will need to be stored for
English, French (with room for future languages as well).

 

I would also like congruity between language, i.e. if you pick Engineer
occupation type in French or English, I want your user record to have the
same code (i.e. whether picked Manager(En) or Gerant(Manager in French), the
user's occupation should=5 (the code for Engineer/Gerant)

 

Obviously this poses a problem for 'normal' relational-databases as one
record can have one ID and I don't think having a FrenchName  EnglishName
for each would be that efficient.

 

My partner has suggested not using a traditional database but a combination
of mySQL and XML.

The mySQL would basically store user records, with codes for most options
(i.e. Occupation would = a number from 1 to 20, salary would = a number from
1 - 5 (representing a salary range), while the XML would store the meaning
of these codes (i.e. we would run an sql query to get the number 5, then run
that past the xml to get the named value (i.e. Engineer) for it.

 

Similarly for the Web Content management, we'd store most of it in XML.


I hope this post makes sense to you. My general preference is to stick to a
normal relational database. Which is the better approach, a big database or
the dbase/xml version.

 

 

   Ben