Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

Database design

Quote Reply
Database design
Hi all,

I have a little logic problem.
Lets say I have a Users Table in my SQL Database. This table has fields for Name, email etc. Now I'd like to have fields for Qualifications, Abilities.

I have a Table of different Qualifications

1 Qualific.1
2 Qualific.2
3 Qualific.3
.
.
.
n Qualific.n

I have a Table of different Abilities

1 Ability 1
2 Ability 2
3 Ability 3
.
.
.
n.Ability n

Now each user should be able to have as many Qualifications as he wants. As well as abilities.

How should I save this information?

Make a field User.Qualification and store the ID's of the Qualifications there? (in a list? 1,4,5,78,192...) and how long should this fieldlength be? What happens if it's full? Print an error "sorry you are overqualified!"?

or make extra tables with the relations:

UserId
Qualific. ID

Does anybody know a more elegant way to solve this?


regards, alexander


Quote Reply
Re: Database design In reply to
In Reply To:
Now each user should be able to have as many Qualifications as he wants. As well as abilities.

How should I save this information?
So, you want a 1->M connection between the Users table and Qualifications table, right?? I would do the following:

1) Make sure that you have an ID field that is auto-incremented in the Qualifications table.

2) Then add a field in your Users table that is the primary key called UserID, then in your Qualifications table, you can use the UserID as a foreign key field that will allow USERS to have MANY qualifications.

Then to print the data, all you do is use the get_record function to pull data based on the foreign key of UserID.

Regards,

Eliot Lee

Quote Reply
Re: Database design In reply to
Hi Eliot,

thanks, I think this could be the right aproach. Do you use foreign keys or have any experience with them?

I am a little unshure because of:

http://www.mysql.com/...n=Broken_Foreign_KEY

regards, alexander

Quote Reply
Re: Database design In reply to
In Reply To:
Do you use foreign keys or have any experience with them?
Yes...in the 1->M tables, like User_Reviews, I have the following structure:

Users

Userid (PK)
Username
Author
Password
Email
Email_Permission
Username_Permission

User_Reviews

ID (PK)
UserID (UNIQUE - FK) (*)
ReviewTitle
Review
SiteRating

(*) You can either set-up as a PRIMARY key if you use the setup.cgi file or UNIQUE if you use editor.cgi or MySQLMan.

Here is an example of 1->1 connected tables I am using:

Links

ID (PK)
UserID (UNIQUE -> FK)
Title
Description

Banners

ID (PK)
LinkID (UNIQUE - FK)
UserID (UNIQUE - FK)
BannerImage
BannerWidth
BannerHeight
BannerAltText
Hits
Views

This means that there is only ONE banner per Link and Users can have multiple Links.

Another example is my Editor_Reviews:

Editor_Reviews

LinkID (PK)
UserID (UNIQUE - FK)
Review

This means that Editors can only review one link once.

I know this may seem messy...

I would highly recommend using MS Access to map out the table relations. It is very helpful for me...I did this with the default tables of Links SQL and I noticed that it violated many rules of database design (including normalization, connections, etc.). So, I went ahead and adjusted the tables...with 1->1 and 1->M connections.

Regards,

Eliot Lee

Quote Reply
Re: Database design In reply to
Hi Eliot,

thank you, I will give it a try..

regards, alexander