Relational database design

[deletedUser2352352]

Ok i'm building a portfolio archive for myself and was looking at the tutorial http://www.kirupa.com/developer/php/relational_db_design.htm.

Now it sort of makes sense doing it like this but i haven't got great logic.

Correct me if i'm wrong doing it like this allows me to store information that would otherwise be duplicated once and search through my portfolio easier?

But i can't seem to get my head round using it for myself? Could someone explain this in baby language how it works. I know you have joiners and things but whats it all for?

Also when adding work into the database how would you add it into that as it need to update three tables and i'm only used to updating one.

Any help would be great and ver much appreciated.

[deletedUser2352352]

ok i've figured it out now.

I have a new question though now.

From a form how do i put the information into more then one table in the datebase.

So for example when submitting my upload form for say work
Within this form i have sent a HTTP_POST('CATEGORIES'), HTTP_POST('CLIENT'), HTTP_POST('WORK') for example. How do i submit the data into each of these plus add the information into the linking table?

This data would have to be split into into these tables "tbl_works" and "tbl_works_catagories" and "tbl_works_clients" the last two are the linking tables see.

See to me this is where this database design becomes a problem

[skrolikowski]

You just have to have two INSERT queries if you want to submit data into two tables. Maybe I'm not understanding your question. Do you have some code you could post?

[deletedUser2352352]

thats the other problem i'm in the design phase so i'm just planning and doing the theory behind it.

From what i can see if i have three tables:

tbl_works = holds all the work
tbl_categories = holds all the work catagories
tbl_works_categories = joins tbl_works and tbl_catagories

when i upload work i'll have to add the work to tbl_works table then also add to tbl_works_categories what id that work is and what that works categories id was

So each item of work in tbl_works_categories has its own tbl_works_categories_id the tbl_works_id and the tbl_categories_id

Does that more sense? Sorry i know this is confusing but my logical brain doesn't cope well with this stuff

[bwh2]

yeah, you're just going to use multiple INSERT statements. one php function you may find useful is [d-php]mysql_insert_id[/d-php].

[deletedUser2352352]

Cheers i thought there may of been something more complex then that.

bwh. How would you use the mysql_insert_id function for yourself?

Thanks

[skrolikowski]

Here's what I'd do.

Create just two tables: tbl_works and tbl_categories

tbl_works should include a column called "work_categories" or something like that, with an integer type. This column will related to the category table's primary_id.

The advantages of this is that if you ever what to update a category, then you just update a single row in the Category table instead of having to update multiple tables.

[deletedUser2352352]

i thought that was the point of having

tbl_works
tbl_categories
tbl_works_categories

So if i needed to change anything all i would have to do is change categories once and the data would still be linked and in tact?

[bwh2]

Create just two tables: tbl_works and tbl_categories

tbl_works should include a column called "work_categories" or something like that, with an integer type. This column will related to the category table's primary_id.the disadvantage being that you can't have a work in more than one category.

i use mysql_insert_id like this:
1) insert into tbl_works
2) mysql_insert_id gets the automatically generated work_id from tbl_works
3) insert into tbl_works_categories using the work_id from (2) and the appropriate category_id's

in my method of using all 3 tables, if you want to update a category, you only need to change tbl_categories.

[deletedUser2352352]

thanks bwh2

This is now making more sense why using this system is better then cramming it all into one table.

Its more flexible and allows you to add more items in if needed with ease.

Cheers

[deletedUser2352352]

ok i have a new question regarding database design

Say i had two areas. A general search area and a admin area.

The sreach area will allow people to search through all the work.
And the admin area would allow you to add catagorys and clients work areas.

Is it bad practice to have small linking tables say linking only certain tables together for the admin section and have one large linking table for the search area linking all the tables?

To me this would be logical but there would be duplication of data but only linking data?

So when you upload work it still only updates two tables at a time.

[bwh2]

for the purpose of this discussion, when you say "search" i'm going to assume you actually mean "browse." if you were building an actual "search," you would need a few more tables for your index and whatnot.

anyhow, when you make an admin section, you will be referring to the same tables as the public section does. each one will access the db through it's own user. the public user should only have SELECT privileges. the admin can have INSERT, UPDATE, SELECT, (i prefer to give nobody DELETE or DROP privileges).

you're not really linking the tables in the actual database. to some extent you can do this, but really you're linking the tables via query (JOINs). so how many and which tables are linked depends on what interface you give to the code. so if you only allow the public section to look at certain tables, then that's all they'll see. same goes for admin.

so think about interface this way: you don't want joe schmoe updating your clients table. so you don't give him any code to do so - you don't put it in his "interface." to him, that functionality doesn't exist. but you do include update functionality in the admin "interface." the admin interface includes the ability to update the clients table while the public interface does not. you further prevent the public from updating the clients table by not giving the public interface UPDATE privileges.

does that make sense?

[deletedUser2352352]

when i say search i mean filter. Sorry

I think what your saying makes sense.

the way i see it is like this.

In the admin section i need too add a new client to (tbl_clients). When i've done this i also need to say what region that client is from (tbl_region).

So it would make sense to have a linking table (tbl_clients_region)to link the client to the the region they are in. eg:

tbl_region = holds geographical regions based in engalnd (no need to update)
tbl_clients = holds client name and client_id (update this one with new client)
tbl_region_clients = links the client to its region (update this one with linking data)

when i add work into the database for example i would only need to do this

tbl_client = holds name and client_id (no need to update)
tbl_works = holds work done data image and what nots (update this one with new work)
tbl_client_works = links the work to the client (update this one with linking data)

But when a user filters thorouh the work

i need them to choose a region then a client then the work.

so would it be easier to have a linking table joining up all the filter criteria or use the two linking tables from before.

Now the above is just an example but the principles remain the same

Does that make more sense?

[bwh2]

So it would make sense to have a linking table (tbl_clients_region)to link the client to the the region they are in. eg:

tbl_region = holds geographical regions based in engalnd (no need to update)
tbl_clients = holds client name and client_id (update this one with new client)
tbl_region_clients = links the client to its region (update this one with linking data)exactly.


so would it be easier to have a linking table joining up all the filter criteria or use the two linking tables from before.no, you would just keep tacking on criteria to a query. so your order of operations might look like this:
1. User picks region #7
2. You present user with filtered results:


/* clients in region 7 */
SELECT c.client_id, c.client_name
FROM tbl_regions a
INNER JOIN tbl_regions_clients b
ON a.region_id = b.region_id
INNER JOIN tbl_clients c
ON b.client_id = c.client_id
WHERE a.region_id = 7
3. User picks client #5
4. You present user with filtered results:


/* works for client 5 */
SELECT a.*
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
INNER JOIN tbl_clients c
ON b.client_id = c.client_id
WHERE c.client_id = 5

5. User picks work #3
6. You present user with filtered results:

/* gets work 3 data */
SELECT * FROM tbl_works WHERE work_id = 3

you following me?

[deletedUser2352352]

I think i do now. Its looking at it in a logical way.

So what your saying is use the smaller linking tables created to filter through to get the results needed.

Is there a limit on how many joining tables that can be queried at one time?

Ok here's a new one.

Say if i wanted to display all the work for that region regardless of the client? How would i use the exsiting database design to to do that?

existing tables

tbl_region = holds geographical regions based in england
tbl_clients = holds client name and client_id
tbl_region_clients = links the client to its region

tbl_client = holds name and client_id
tbl_works = holds work done data image and what nots
tbl_client_works = links the work to the client

My brain would say look at all the clients id from tbl_region_client that relate to that region then all the works id that relate to the clients from the region.

this is now confusing

Thanks for all your help though its much appreciated

[bwh2]

So what your saying is use the smaller linking tables created to filter through to get the results needed.

Is there a limit on how many joining tables that can be queried at one time?your linking tables just help you join the data together. i know of no limit. i've joined together about a dozen tables before and been fine.

Say if i wanted to display all the work for that region regardless of the client? How would i use the exsiting database design to to do that?
/* get the works for clients in region 6 */
SELECT a.*
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
INNER JOIN tbl_regions_clients c
ON b.client_id = c.client_id
WHERE d.region_id = 6

/* same results as above, query written slightly different
this format might be less confusing */
SELECT a.*
FROM tbl_works a,
tbl_works_clients b
tbl_regions_clients d
WHERE a.work_id = b.work_id
AND b.client_id = c.client_id
AND c.region_id = 6

[deletedUser2352352]

see looking at it like this now all makes sense. Cheers for all your hard help.

It looks difficult to understand but once you get your head round it its just logical.

BRILLIANT

[deletedUser2352352]

just a quick question whats this a b and c thing thats going on?

I think its a alias sort of thng. But in the above code what does a.* stand for?

[bwh2]

yeah, it's just an alias. i use it so i don't have to retype the complete table name. so these 2 queries are the same:


/* with aliases */
SELECT a.*
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
AND b.client_id = 2



/* without aliases */
SELECT tbl_works.*
FROM tbl_works
INNER JOIN tbl_works_clients
ON tbl_works.work_id = tbl_works_clients.work_id
WHERE tbl_works_clients.client_id = 2
aliases just make things faster to type.

* just means all columns. so a.* is saying to select all columns from table with the alias a.

[deletedUser2352352]

I bet i sound so stupid ha ha ha

Cheers dude

[bwh2]

no, it's cool. keep posting if you have more questions.

[deletedUser2352352]

wicked thanks a lot. I think database design is important, you can cut out a load of short falls before they arise and you only have to learn it once.

[bwh2]

exactly. we should all design a db expecting it to grow and change.

[deletedUser2352352]

i've actually archived this thread as its most useful now.

[deletedUser2352352]

I use MySQL and phpMyAdmin. Is there anything i need to do to set up foreign keys on this or are they needed in that sense?

[bwh2]

it's kind of funny because i never explicitly state PK->FK relationships. but i think you can do it like this:
1. go to a table's Structure view
2. click Relational View
3. select appropriate Internal Relation(s)
4. to check, view the database Structure. then click Data Dictionary

[deletedUser2352352]

well its worked using one of your sql querys straight into php sql part without a hitch. So i'm wondering if i should bother. I'll take a look into it though.

On a note i now set up a simple version linking to tables with a linking table and it works great i can see the benifits of the whole system and its easier to sort through and much faster.

[deletedUser2352352]

Now for some reason every time i try and use mysql_insert_id(); it just throws back a zero at me.

This is the code i'm using to do the insert



function addclient($client, $region){
if ($_SESSION['thegroup'] <= 2){
// Create an SQL query (MySQL version)
$insertQuery = "INSERT INTO tbl_client (client_name) VALUES ('$client')";
// Save the form data into the database

$client_id = mysql_insert_id(); \\ not working just throwing back zero

$insertQuery = "INSERT INTO tbl_regions_client (region_id, client_id) VALUES ('$region','$client_id')";
if ($result = $connector->query($insertQuery)){
// It worked, give confirmation
return "The client has been added to the database";
}else{
return "Sorry, there was an error saving to the database";
}
}else{
// not enough admin privileges
return "not enough privileges to do this action";
}
}

[deletedUser2352352]

Sorted i had it in the wrong place



function addclient($client, $region){
if ($_SESSION['thegroup'] <= 2){
// Create an SQL query (MySQL version)
$insertQuery = "INSERT INTO tbl_client (client_name) VALUES ('$client')";
// Save the form data into the database

//$client_id = mysql_insert_id(); shouldn't be put here

if ($result = $connector->query($insertQuery)){

client_id = mysql_insert_id(); // here instead

// It worked, give confirmation
return "The client has been added to the database";
}else{
return "Sorry, there was an error saving to the database";
}
}else{
// not enough admin privileges
return "not enough privileges to do this action";
}
}

[deletedUser2352352]

in the database query


/* without aliases */
SELECT tbl_works.*
FROM tbl_works
INNER JOIN tbl_works_clients
ON tbl_works.work_id = tbl_works_clients.work_id
WHERE tbl_works_clients.client_id = 2

How would i add COUNT(*) to count how many items of work there are for that client.

I keep getting an error and would like to say something if that client has no work attached to it.

[bwh2]

if the client doesn't have any work associated with it, there will be no rows in that result set. if you want to find how many works are associated with a client, you could do:

SELECT tbl_clients.client_id,count(tbl_works_clients.work _id) as numWorks
FROM tbl_clients
LEFT OUTER JOIN tbl_works_clients
ON tbl_clients.client_id = tbl_works_clients.client_id
GROUP BY tbl_clients.client_id

[deletedUser2352352]

How would you put the count on this then.


/* get the works for clients in region 6 */
SELECT a.*
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
INNER JOIN tbl_regions_clients c
ON b.client_id = c.client_id
WHERE d.region_id = 6

/* same results as above, query written slightly different
this format might be less confusing */
SELECT a.*
FROM tbl_works a,
tbl_works_clients b
tbl_regions_clients d
WHERE a.work_id = b.work_id
AND b.client_id = c.client_id
AND c.region_id = 6

As i've been using this. Sorry. You've added a new query in and i don't know what its for?

LEFT OUTER JOIN

[bwh2]

adding a count there won't do you any good because it would be a COUNT on a single work, not for all of the works.

the COUNT actually relates to the client. we want to say "client X has Y works associated with it". that's what my query gives you. if we added a COUNT to the query you are mentioning, we would be saying, "work X has Y work/client combinations associated with it". i know it sounds confusing, but that's what it is.

in my query, i used a LEFT OUTER JOIN because i wanted the client_id to still appear even if the client didn't have any works associated with it. with an INNER JOIN, the client_id would only appear if the client_id had one or more work_id's associated with it (in tbl_works_clients). LEFT and RIGHT OUTER JOINS will pull back NULL values in the direction of the join. b/c mine was a LEFT OJ, it will pull back values from the table on the left of the join statement (tbl_clients), even if there is no corresponding data from the table on the right side of the join statement.

[deletedUser2352352]

i'm not sure i understand.

By using LEFT OUTER JOIN your saying that even if there is nothing associated with it it would still return a value?

so left and right relate to top and bottom of the join.



SELECT
FROM
LEFT OUTER JOIN
ON
RIGHT OUTER JOIN
GROUP BY


Also whats this GROUP BY statment used for?

Sorry for being a pain

[bwh2]

it's no pain. i'm happy to help.

perhaps it makes sense to look at the outer joins like this:


/* pull data from left table even if no corresponding data in right table */
SELECT ...
FROM tbl_left LEFT OUTER JOIN tbl_right
ON ...

/* pull data from right table even if no corresponding data in left table */
SELECT ...
FROM tbl_left RIGHT OUTER JOIN tbl_right
ON ...

so literally you are looking at the tables to the left and right of the JOIN statement.

you use GROUP BY when selecting from an aggregate function like COUNT,SUM,MIN,MAX,etc. along with one or more column(s). so for instance:


/* do not need group by */
SELECT COUNT(*)
FROM tbl_works

/* needs group by */
SELECT client_id,COUNT(*)
FROM tbl_works_clients
GROUP BY client_id

/* needs group by. gets number of works per client even if client has zero works */
SELECT tbl_clients.client_id,tbl_clients.client_name,COUN T(*)
FROM tbl_clients
LEFT OUTER JOIN tbl_works_clients
ON tbl_clients.client_id = tbl_works_clients.client_id
GROUP BY tbl_clients.client_id,tbl_clients.client_names

so the data is broken into groups. in the first case, the group is client_id. in the second case, the grouping is by client_id and client_name.

[deletedUser2352352]

that makes sense cheers for this.

[bwh2]

no problem. let me know what other questions come to mind.

[deletedUser2352352]

hello

I have a new question ha ha ha

I've got it so its pulling all the work for that region how do i display the clients name as well so its like a list?

for example i want all the work to display for region one in a long list but in the table i would like to show the clients name next to it as well. Before i chose i client to look at further where it would only show me all the work for that client. Does that make sense?

I'm using this at the mo which is great for pulling all the work for that region but would be nice to just display client name as well.



SELECT a.*
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
INNER JOIN tbl_regions_clients c
ON b.client_id = c.client_id
WHERE d.region_id = 6

[bwh2]

SELECT a.*,d.client_name
FROM tbl_works a
INNER JOIN tbl_works_clients b
ON a.work_id = b.work_id
INNER JOIN tbl_regions_clients c
ON b.client_id = c.client_id
INNER JOIN tbl_clients d
ON b.client_id = d.client_id
WHERE c.region_id = 6^just add an INNER JOIN to tbl_clients because that's where client_name is stored. also, region_id was coming from tbl_regions_clients (c) so i changed that in your where clause from d to c.

[deletedUser2352352]

sweet mate that does the trick. I'm starting to know now how to do the joins and work it out.

[bwh2]

cool. it's a lot more fun when you begin to understand exactly what's happening.

[deletedUser2352352]

i'm surpised by how easy it makes evrything. It feels faster and a lot more slick as well.

You only have to do one query and get the results once and not mess around with it all.

I've already seen a benifit as well i had to change the name of a client i did it once and bash its done.

Smashing really good stuff

[bwh2]

yeah. that's the beauty of a relational design. it also saves you time when working with the data in your server-side language like PHP or ASP.

[deletedUser2352352]

New question:

Ok so i have evrything woking now at its all great i understand whats going on.

I'm able to make the search dynamic using $variables and the such.

My question is this. Whats the best way to leave a value on the sql so that if nothing is chosen it defaults out everything?


Ok wont make sense as such but hopefully will make it clearer(my quicktags didn't respond sorry about that)

$variable = VALUE or NONE

SELECT a.*
FROM tbl_works a,
tbl_works_clients b,
tbl_regions_clients d,
tbl_blah e
WHERE a.work_id = b.work_id
AND b.client_id = c.client_id
AND c.region_id = 6
AND e.blah_id = $variable

But if i want that to pull everything out what would i replace that $variable with?

Hopefully that will make some sense.

[bwh2]

so you're saying if that query doesn't produce any results, just get everything from those tables joined together? you would use PHP to count the rows (like [d-php]mysql_num_rows[/d-php]). if no results, rerun the query without that the criteria.

[deletedUser2352352]

hmm

What i mean is if that $variable contains nothing to just pull all the data from tbl_blah almost ignoring that part. But if $variable does contain data to use that within the sql?

Does that make sense?

I've done it like this so far but feel its dirty and insecure.

if( $variable1 == '1'){
$variable2 = '';
}else{
$variable2 = '="'.$variable3.'"';
}

then either looks like this

tbl_blah $variable

tbl_blah ''

or

tbl_blah ="'.$variable3.'"

[bwh2]

do something like...


$sql = "SELECT a.*
FROM tbl_works a,
tbl_works_clients b,
tbl_regions_clients d,
tbl_blah e
WHERE a.work_id = b.work_id
AND b.client_id = c.client_id
AND c.region_id = 6";

if( $variable ) {
$sql .= "AND e.blah_id = '$variable'";
}

$result = mysql_query( $sql );


so just concatenate if the variable is defined.

[deletedUser2352352]

wicked cheers for that

[bwh2]

no problem. i find that method pretty handy when i have several optional criteria.