Gossamer Forum
Home : Products : DBMan : Discussions :

What kind of database should I make - Single or Relational?

Quote Reply
What kind of database should I make - Single or Relational?
I am the webmaster of http://www.colombiaklubben.no. It is the web for Colombiaklubben (Norway/Europe) - A club for families with children adopted from Colombia.

I want to make a database to hold the members addresses and personal info.
All the members will not use all the fields. We have for instance families who are waiting for their first child, members with 2, 3, 4 or 5 children (6 is in case someone gets 6 children) Other fields some people will not use are Email, phone ...

What kind of database should I make - Single or Relational?
The database will in single DB hold a lot of fields.

Is there other things I should take into consideration?


These are the fields I want to use:

Single DB:

Field_name
ID
Username
Date (new)
Date (modefied)
Validated
Rolle
Mothers name
Fathers name

Name 1st child
Birhplace 1st child
Birthdate 1st child
Adoptiondate 1st child
ICBF or private orphanage
If private orphanage - Name of orphanage
- Similar fields for child 2 - 6
Address (street)
Address (zip)
Address
Town/City
County
Telephone private
Telephone mobile
Telephone mobile
Email (Mother)
Email (Father)
Homepage URL
Presentation


Relational DB:

Field_name
IDFamily
Username
Date (new)
Date (modefied)
Validated
Rolle
Address (street)
Address (zip)
Address
Town/City
County
Telephone private
Homepage URL
Presentation

Field_name
IDMother
Username
Date (new)
Date (modefied)
Validated
Name
Telephone mobile
Email
Homepage URL

Field_name
IDFather
Username
Date (new)
Date (modefied)
Validated
Name
Telephone mobile
Email
Homepage URL

Field_name
IDChild1
Username
Date (new)
Date (modefied)
Validated
Name
Birhplace
Birthdate
Adoptiondate
ICBF or private orphanage
If private orphanage - Name of orphanage
Email
Homepage URL

Similar DB for child 2 to 6 using IDChild2 - 6)

Jan Peter
Quote Reply
Re: [JPWiese] What kind of database should I make - Single or Relational? In reply to
Just a suggestion but I think unless you were going to have a large number of entries you may just want to have it all in one database.

If you were going to go with the relational I would suggest all the contact information in the main database, and then group all the children information into a second. No need to make a database for each child. For example:

Main:

Username - keyfield
PostDate
ModDate
Validated
Rolle
Last Name
Mothers name
Fathers name
Address (street)
Address (zip)
Address
Town/City
County
Telephone private
Telephone mobile
Telephone mobile
Email (Mother)
Email (Father)
Homepage URL
Presentation

Children:

Username - association between dbs
ChildrenID - keyfield
PostDate
ModDate
ValidatedC

Then repeat these fields for each child

Child_Name1
Birhplace1
Birthdate1
Adoptiondate1
ICBF1 (yes/no)
Orphanage1

Just an idea .. perhaps others will add their ideas.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/
Quote Reply
Re: [LoisC] What kind of database should I make - Single or Relational? In reply to
Thank you LoisC.
After what you said I think I will go for the single database(all in one) It is some 200 member families today and I don't think it will be more than 300 families.

I have a few new questions:

1) What is the difference between the fields Keyfield and Userid as mentioned in JPDeni's configurator?
2) How can I have the system to get the date (PostDate) automatic and keep it unchanged?
3) How can I have the system to get the date (ModDate) and change it every time the record is modefied?
4) How do I set up validation so I or another admin can validate the new or modified record before its visible?
5) We have a lot of members addresses and info today in htmlpages. Is there a way I can register it myself in the database and change owner after the members having registered with teir own UserName?


These are the fields I will use:

UserID - keyfield
UserName
PostDate
ModDate
Validated - Select field (yes/no)
Rolle - Select field (Medlem/Sekretær/Colombiakoffert/Ungdomskontakt/Kasserer/Salgsgruppa/Webmaster)
Mothers_name
Fathers_name

Child_name1
Birhplace1
Birthdate1
Adoptiondate1
ICBF1 - Select field (yes/no)
Orphanage1
- Child_name2 - 6
Address_Street
Address_Zip
Address_City
Address (Bostedskommune)
County (Fylke)
Telephone_private
Telephone_mobile1
Telephone_mobile2
Email1
Email2
Homepage_URL
Presentation


JPWiese

Jan Peter
Quote Reply
Re: [JPWiese] What kind of database should I make - Single or Relational? In reply to
1) What is the difference between the fields Keyfield and Userid as mentioned in JPDeni's configurator?

A key field can be used as a counter for instance to keep track of record IDs. This field is used so the script can associate the records within your database when using links or the forms.

Your userid field would be the login name to be able to associate records with their 'owner'.

2) How can I have the system to get the date (PostDate) automatic and keep it unchanged?

In your .cfg file set the field date as:

'PostDate' => [ 4,'alpha',35,35,&get_date,'',''],

3) How can I have the system to get the date (ModDate) and change it every time the record is modefied?

In html.pl, sub html_modify_form_record, just after the line that starts with

if (!%rec) { &html_modify_failure (

add

$rec{'ModDate'} = &get_date; # Last modified date

4) How do I set up validation so I or another admin can validate the new or modified record before its visible?

You would install the validation mod which is available in either the GT Resource Center or JPDeni's website http://jpdeni.com/dbman/
Just follow the instructions carefully to setup the mod.

5) We have a lot of members addresses and info today in htmlpages. Is there a way I can register it myself in the
database and change owner after the members having registered with their own UserName?

To setup the script to automatically insert the UserID when someone is logged in and adds a record you would make the following changes. What this does it sets the userid to the logged in userid unless admin is adding a record. You could have people signup and then provide you with the UserID afterwards (via a form you provide, etc) and then modify the record to add their UserID.

Add userid automatically when adding records - make the following changes in db.cgi:

In sub get_defaults replace:

foreach $field (keys %db_defaults) {
$default{$field} = $db_defaults{$field};
}

with:

foreach $field (keys %db_defaults) {
$default{$field} = $db_defaults{$field};
####### add userid in add form ###########
unless ($per_admin) { ($auth_user_field >=0) and ($db_defaults{$db_cols[$auth_user_field]} = $db_userid); }
}


In sub add_record instead of:

# Set the userid to the logged in user.
##### ($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid);

use:

# Set the userid to the logged in user (except if admin).
unless ($per_admin) { ($auth_user_field >= 0) and ($in{$db_cols[$auth_user_field]} = $db_userid); }

Have you checkout out the DBMan FAQ noted below? All of the answers to your questions can be found within the FAQ.

Unoffical DBMan FAQ

http://creativecomputingweb.com/dbman/index.shtml/
Quote Reply
Re: [LoisC] What kind of database should I make - Single or Relational? In reply to
Thank you for your help LoisC. I will set up my database now (and read the FAQ *S*)

JPWiese

Jan Peter