Page 1 of 1

help: mapping ER to database

Posted: Sun Nov 02, 2008 2:27 am
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.

Re: help: mapping ER to database

Posted: Sun Nov 02, 2008 6:08 am
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

Re: help: mapping ER to database

Posted: Sun Nov 02, 2008 4:34 pm
by ijlal2
thanks VladSun. Thats nice.