Database design

I cant get my head around the way my superiors want my database to work...

Here is an example they want to work
Type in animal as a search and
Animal comes up - click on that and it branches
out to Dog, Cat, Duck etc then click on one of those
and it branches out to Wandering, missing, Feeding.

My initial idea was to have a main table with top headings such as Animal, Accounts, IT (areas of council)
then tables for each of the branches such as Dog,Cat,Duck
then tables for each of their branches such as wandering, missing.

However this could end up in thousands of tables and I think it would be a hell of a search because I dont know how to search all the tables.

Anyone got any ideas how this should work?
It confuses me!

that'll only be 3 tables - is that too much?

christo

actually just one table, with a recursive hierarchy, like the good old who-reports-to-whom employee table, or the category-subcategory-subsubcategory table, or...

the problem with three tables is that if you want to "promote" duck to the top level, you have to move it from one table to the other, but even worse, you have to move all its subcategories from the 3rd table up into the 2nd, and there goes all your pointers/links...


rudy

Presuming your db supports temp tables, access the data through a stored procedure, read the minimum amount of data into a temp table (it will likely have much more than what you are looking for, but ideally much less than the entire table(s)), index the temp table and run another query on it. Presuming you have adequate indexes on your master table(s), this should run quite quickly. I have done the same sort of thing where I do my own joins because the query optimizer (in Sybase, in this case, though the same thing does happen in MS SQLServer) is too brain dead to figure out the most efficient method.

You can use the exact same stored procedure, it just performs differently each time based on the input.

please note Im a beginner database builder(ie this is the first database i've ever built)

so to be honest I dont really understand what you are talking about mitakeet.

The recursive hierarchy table sounds like the best way to achieve what I need.
Can anyone point to me some resources for implementing something like this?

Thanks for all your responses anyway

The recursive relationship
http://searchdatabase.techtarget.co...x285649,00.html
free registration may be required

rudy

You r going 2 have 1 table looking something like this:

intID vcDescription intParent

1 Animals 0
2 Dog 1
3 Cat 1
4 Duck 1
5 German Shephard 2
6 Cross Breed 2

etc.

That way when someone clicks on animals, you get the id (which will be 1), and the just get everything whose Parent is the same as the ID.

Can this be done using an Access database?

Its going to be used through the company Intranet so what sort of interface would be best for coding?

I was hoping to use PHP.

How would I work it so that if someone clicks on animals it displays all the children and so oN?

I've been trying to look for tutorials on this and they mostly just explain what a recursive hierarchy table is not how to implement it

The search terms dont seem to return much relevant information.

Thanks again

You're asking a few questions in one. I think the isues here are:

1. How to design the database
2. How to actually set up the database in mysql or access or whatever
3. How to get a web page to be an interface to the database


If this is really your first time doing a web/database project, start with a simple example like silian said; here's one of a management/reporting structure:

 id - name - boss
101 - carl - 101
102 - josh - 101
103 - bets - 104
104 - luke - 102

In this case Carl is his own boss (he's the top boss).
Josh reports to Carl
Luke reports to Josh
Bets reports to Luke

1. make a table called HR
2. add those columns and records as I described above
3. write up some sql select statements for things you might want to do, such as:

find all 2nd tier people: select id, name from HR where boss=101
find all people who have a manager: select id, name from HR where boss != 101
find all people who are the boss of someone else: select id, name from HR hr1 where id IN (select distinct boss from HR hr2)

Now comes the hard part: read up in http://www.php.net to learn basic PHP and then http://us2.php.net/manual/en/ref.mysql.php to learn how to use PHP to talk to MySQL. I'm not sure whether PHP can talk to MS Access. Make some pages with links that call your queries. Try some different application designs; perhaps you'll have a different page for each task, or perhaps you'll have one page which dynamically includes whatever functions it needs.


Once you've done this practice exercise, you'll be ready to start working on the real project.

Good luck!


PS your database description was kind of vague. There are different things I'd recommend, based on how often the categories and data will change, and how important speed is to your database. If you let us know more, we may have more relevant help available to you.

another thing to consider is not to store data by using descriptive table names. If you're storing 3 kinds of people, lets say Doctors, Teachers, and Workers; don't make 3 tables. Make one table called Person, and have one column be called "personType", and it can have a value of doctor, teacher, or worker.

If you're into object oriented database design, try to think of your database-world as objects with attributes. A doctor is a person with a persontype of doctor. A teacher is a person with a persontype of teacher, but otherwise, they're the same as a doctor; in that you need to know their name, address, phone, etc.

The reason my database description is vague is because thats the only description I've been given and I have to work out what to do based on that. to be honest I wish i had more information on what Im supposed to make but my boss seems to expect me to know. I believe the data will be changing frequently btw.

I have basic PHP skills and yes Access can interact with PHP because I have already achieved that.

Im just unsure how to search the KeyTerms field and then return the record/s with a link for each so that I can click on that link and then return all those further down in the hierarchy. so basically how can I call a query by clicking on a link?

another question.

With the recursive hierarchy database if I have say 10000 records in the one table am I going to get sufficient speed when querying??

Thanks in advance

dont mean to sound sucky but people are willing to offer so much of their knowledge here! its great

Do you know how to pass parameters into a PHP document by putting them at the end of the url?

You can send in one param to tell it to do a certain pre-written query, and a second param to tell it to filter that query by the type the user clicked.

So your url might be:

http://www.myplace.com/app.php?acti...a&filter=Canine


A php function at the beginning of your page would read these url params and act accordingly; executing the QueryBeta query with "and type='canine'" appended to the end of it. Then you'd use the result set to create the records listed on the page.

See http://us3.php.net/variables.external for how to read parameters from the url using $HTTP_GET_VARS