help: mapping ER to database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ijlal2
Forum Newbie
Posts: 6
Joined: Sun Jul 06, 2008 2:06 pm

help: mapping ER to database

Post by ijlal2 »

hi, i am a newbie to databases.
I have a table called ' records'.
with fields as
id bios computer ram CDROM

id is the primary key of the table 'records'.. Now each id is also related to other id(s) of the same table.. like

like id=3 can be related to id=4,5,6,7,8 ... How can i store this information records table.. Currently i am adding a new column in table 'records' names as related_entries and storing the related ids and comma seperated. is this a good approach..?? or there is some better ways.. i want this schema to be very good use of db concepts..

id bios computer ram CDROM related_entries
3 OK OK OK OK 4,5,6,7,8,9,0
4 OK OK OK OK 7,3,5,6,7
5 OK OK OK OK 55,22,33,2

thanking in anticipation.
regards.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: help: mapping ER to database

Post by VladSun »

ijlal2 wrote:Currently i am adding a new column in table 'records' names as related_entries and storing the related ids and comma seperated. is this a good approach..?? or there is some better ways.. i want this schema to be very good use of db concepts..
No, it's not a good approach.
You should remove the column and create a new table related_records instead.
The table should look like this:

Code: Select all

records_id (int) 
related_records_id (int)
So, in this table you have to create a single row for every relation id.

If you have record with id=1 and related records with id=2,4 then:

records table

Code: Select all

id | ....
1  | ....
2  | ....
3  | ....
4  | ....
related_records table

Code: Select all

records_id | related_records_id 
1 | 2
1 | 4
There are 10 types of people in this world, those who understand binary and those who don't
ijlal2
Forum Newbie
Posts: 6
Joined: Sun Jul 06, 2008 2:06 pm

Re: help: mapping ER to database

Post by ijlal2 »

thanks VladSun. Thats nice.
Post Reply