Bridge linkage/FK Problem
i faced a problem, i used to develop several portal, without link/FK those tables even they hv relationship. So i write my program without concerning a db design rule, like, i even design a table like this
Table: Job
Field:
- id
- member_id
- posted_by (admin,member)
- job_content
--------------------------
if it's posted by admin, member_id field will store NULL, but
if it's posted by member, them member_id will be store with value.
--------------------------
it works fine if i code the system without any using any framework, or FK up member and job table. But, now i wana try to code system properly. By using symfony(PHP framework), and FK Job table with my Member table, but... i facing problem to insert any record for Job table with storing member_id to null, coz of the FK issue, so, how should i design the DB properly?
Design 1
-------------------------
Table: Job
Field:
- id
- member_id (FK to member table)
- admin_id (FK to admin table)
- posted_by (admin,member)
- job_content
Prob: Still facing same prob, coz either member_id/admin_id cant be NULL
-------------------------
Design 2
Table: Job_by_admin
Field:
- id
- admin_id
- job_content
Table: Job_by_member
Field:
- id
- member_id
- job_content
Prob: Cant select the job data in ONE query
-------------------------
Another Question
=================
What is the best way to design a friend-member struct?
is it
Table: Member
id
name
Table: Friend
member_id (FK to member)
friend_id (FK to member)
So, can anyone guide me what is the best way to design this situation?
Thanks
Edited 1 time(s). Last edit at 05/18/2007 12:45AM by weitat chok.