| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-14-08, 07:27
|
|
Registered User
|
|
Join Date: Jan 2008
Posts: 17
|
|
|
HELP : primary/foreign key
|
|
I have the following table structure:
Scenario 1 is the current situation. I have 3 tables : family, tools and permission.
tbl_family contains roles (father, mother, son)
tbl_home contains tools (tv, laptop) and privileges (use, reinstall etc)
tbl_permission defines permission (fater, tv, use) etc
I wanted to redesign the tables and use ID as it should. In scenario 2 I started, tbl_family is done (by adding field family_id) but how do I define in tbl_home the primary key??
Would tools_id, tools, privilege be primary key all three? and the same for tbl_permission!
hleeeeeeeeeep
|
|
01-14-08, 08:16
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
you should perhaps start over
each table should be about something
so a "home" table should be about the home, not about the stuff like a tv that's in the home
perhaps the tv and laptop might be instances of a "tool" as you say, in which case there should be a tools table
what is the difference between privilege="use" and permission="use"
this is a homework assignment, isn't it
|
|
01-14-08, 14:43
|
|
Registered User
|
|
Join Date: Jan 2008
Posts: 17
|
|
|
|
No it is just for experience. I want to add ID (int) to each table, because I wanted to use key/value for dropdownlist.
tbl_home is having tools such as tv which might have 1 or more privileges (watch, adjust etc). In tbl_permission is the family - home relation is defined including possible privileges (permission) for each tool.
is it more clear now?? So my problem is that in tbl_permission I can't have 3 primary keys (ID, FAMILY, TOOL)
|
|
01-14-08, 15:00
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by internationalist
I want to add ID (int) to each table, because I wanted to use key/value for dropdownlist.
|
so you prefer this --
<select name="mycodes">
<option value="1">one</option>
<option value="2">two</option>
<option value="3">buckle</option>
<option value="7">my shoe</option>
</select>
to this --
<select name="mycodes">
<option value="one">one</option>
<option value="two">two</option>
<option value="buckle">buckle</option>
<option value="my shoe">my shoe</option>
</select>
if so, the difference is only a few bytes in the packet that is sent from the web page to your application, hardly a sufficient reason to me
|
|
01-14-08, 15:05
|
|
Registered User
|
|
Join Date: Jan 2008
Posts: 17
|
|
|
|
01-14-08, 15:13
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
the problem in permission table is that you need the full PK from home table, i.e. both columns, as the FK
it is indeed that simple
|
|
01-14-08, 15:41
|
|
Registered User
|
|
Join Date: Jan 2008
Posts: 17
|
|
Could you provide a simple database design, please?
|
|
01-14-08, 16:17
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Home (tools, privilege)
Family (position)
Permission (tools, privilege, position)
legend: PK, FK
|
|
01-14-08, 16:43
|
|
Registered User
|
|
Join Date: Jan 2008
Posts: 17
|
|
hmm am I not clear?? once again look at current situation http://www.dbforums.com/attachment.p...7&d=1200313234
It is exactly as you have recommended. What I want is something like:
Family ( family_Id, position)
Home ( home_Id, tools, privilege)
Permission ( permission_Id, home_Id?, family_Id)
It is tbl_Home and tbl_Permission that I am not sure how to solve!
|
Last edited by internationalist; 01-14-08 at 17:11.
|
01-16-08, 04:18
|
|
Registered User
|
|
Join Date: Oct 2007
Posts: 3
|
|
would this work? 
family (family_Id*, home_id*, position)
home (home_Id*)
tools (home_id*, tools_id*, tool_name)
toolsactions (home_id*, tools_id*, action_id*, action_name)
familypermission (family_id*, home_Id*, tools_id*, action_id*)
'*' is primary keys
this query
Code:
SELECT
family.position,
IF(familypermission.action_id IS NULL, 'NOT ALLOWED', 'ALLOWED') AS permission
CONCAT(toolsactions.action_name, ' ', tools.tool_name) AS action
FROM
family
INNER JOIN
home
ON
home.home_id = family.home_id
INNER JOIN
tools
ON
tools.home_id = home.home_id
INNER JOIN
toolsactions
ON
toolsactions.home_id = home.home_id AND
toolsactions.tools_id = tools.tools_id
LEFT JOIN
familypermission
ON
familypermission.family_id = family.family_id AND
familypermission.home_id = home.home_Id AND
familypermission.tools_id = tools.tools_id AND
familypermission.action_id = toolsactions.action_id
on
Code:
FAMILY TABLE
family_id home_id position
-----------------------------
1 1 FATHER
2 1 MOTHER
3 1 CHILDREN
HOME TABLE
home_id
---------
1
TOOLS TABLE
home_id tools_id position
-----------------------------
1 1 TV
TOOLSACTIONS TABLE
home_id tools_id action_id action_name
----------------------------------------
1 1 1 FIX
1 1 2 WATCH
1 1 3 ABUSE
FAMILYPERMISSION TABLE
family_id home_id tools_id action_id
----------------------------------------
1 1 1 1
2 1 1 2
3 1 1 3
would result something like
Code:
position permission action
--------------------------------
FATHER ALLOWED FIX TV
FATHER NOT ALLOWED WATCH TV
FATHER NOT ALLOWED ABUSE TV
MOTHER NOT ALLOWED WATCH TV
MOTHER ALLOWED WATCH TV
MOTHER ALLOWED ABUSE TV
CHILDREN NOT ALLOWED FIX TV
CHILDREN NOT ALLOWED WATCH TV
CHILDREN ALLOWED ABUSE TV
or a code like
Code:
SELECT
CONCAT(family.position, ' IS ', IF(familypermission.action_id IS NULL, 'NOT ALLOWED', 'ALLOWED'), ' TO ', CONCAT(toolsactions.action_name, ' ', tools.tool_name)) AS theresult
FROM
family
INNER JOIN
home
ON
home.home_id = family.home_id
INNER JOIN
tools
ON
tools.home_id = home.home_id
INNER JOIN
toolsactions
ON
toolsactions.home_id = home.home_id AND
toolsactions.tools_id = tools.tools_id
LEFT JOIN
familypermission
ON
familypermission.family_id = family.family_id AND
familypermission.home_id = home.home_Id AND
familypermission.tools_id = tools.tools_id AND
familypermission.action_id = toolsactions.action_id
on
Code:
FAMILY TABLE
family_id home_id position
-----------------------------
1 1 FATHER
2 1 MOTHER
3 1 CHILDREN
HOME TABLE
home_id
---------
1
TOOLS TABLE
home_id tools_id position
-----------------------------
1 1 TV
TOOLSACTIONS TABLE
home_id tools_id action_id action_name
----------------------------------------
1 1 1 FIX
1 1 2 WATCH
1 1 3 ABUSE
FAMILYPERMISSION TABLE
family_id home_id tools_id action_id
----------------------------------------
1 1 1 1
2 1 1 2
would result something like
Code:
theresult
--------------------
FATHER IS ALLOWED TO FIX TV
FATHER IS NOT ALLOWED TO WATCH TV
FATHER IS NOT ALLOWED TO ABUSE TV
MOTHER IS NOT ALLOWED TO FIX
MOTHER IS ALLOWED TO WATCH TV
MOTHER IS NOT ALLOWED TO ABUSE TV
CHILDREN IS NOT ALLOWED TO FIX TV
CHILDREN IS NOT ALLOWED TO WATCH TV
CHILDREN IS NOT ALLOWED TO ABUSE TV
|
Last edited by dhanuxz; 01-16-08 at 06:08.
|
01-16-08, 05:57
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Quote:
|
Originally Posted by dhanuxz
would this work?
Code:
...
INNER JOIN
home
ON
home.home_id = home.home_id
|
not that part, no 
|
|
01-16-08, 06:09
|
|
Registered User
|
|
Join Date: Oct 2007
Posts: 3
|
|
aah, here' s the bug fix...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
HELP : primary/foreign key
