What's the best way to set up this database?

Databases
1

I’ve been struggling with this application for too long now, and I’ve done a lot of thinking about it over the weekend, and finally determined that my problem is most likely in the way I have my database laid out. So I’m going to dial back the detail and just ask a simple question. What’s the best way to set up this database?

Right now I have three tables.

Resources

  • ID
  • Name

Topics

  • ID
  • Name

Criteria

  • ID
  • Name
  • Parent ID

Now, I need to figure out the best way to tie these together.

I resource can exist in many topics (many-to-many). A criteria can apply to any of the resources (also many-many) BUT the rub is that the resource-criteria assignment is dependent on topic. A resource might be in criteria 20 in two of the topics, but not the rest.

I thought about an EAV layout, which I am just now getting my head around. But I’m not sure if it would be best, or if there’s something I haven’t come up with yet. Everytime I think I have a workable layout, I end up finding a problem with it!

2

You could set up some kind of intermediary tables, like “relationships” with an id, topic id, criteria id… and just do one entry for every single relationship. Then you’d want to query the relationships table for all entries with that resource id and manipulate your data from there?

More information on what you’re looking for might help - but the above idea might be a start anyway.

i.e.

Relationships

  • ID
  • Resource ID
  • Topic ID
  • Criteria ID
3

Well I started off that way. I started with a table that held the resource ID, the topic ID, and the criteria ID. Where I got hung up was when I built the form out.

The web app we currently came up with was step-based. First you choose your topic, then you choose your first criteria. There are three criteria pages (one for each parent criteria), so you’ve gone 4 steps by the time you get to the resources.

What I was asked to do is remove the criteria for which a result didn’t exist as we progessed through the steps. This is one of the things that’s made this problem a tough one to solve, and in the end that portion might not be easily doable.

So for now I’m just focusing on getting something that works the best way possible and I’ll worry about the air conditioning and cruise control later. :slight_smile:

4

Well if i understand you properly you should have 4 table.

  1. topic
  2. resource
  3. criteria
    4)sub criteria

Topic

  • ID
  • Name
  • CID (criteria ID)

Criteria

  • ID
  • Name

Sub_criteria

  • ID
  • Name
  • CID (criteria ID)
  • RID (Resource ID)

Resources
ID
Name

Here how it’s goes.
First you chose topic, using CID in topic table you can load selected criteria for that topic. then you search sub criteria table with that particular CID.Which load the Sub Criterias for that particular criteria.
here comes the last part , now you have list of sub criterias. these sub criteria has RID…you can search each RID (probably using foreach loop) to get all the resources need for that particular topic.

Hope this will helpful
Happy Codding :smile:

5

Yep I’ve had it set up pretty much like that for a while, and I even tried three intermediate tables to join the criteria. I could get the application to work, just now how they wanted it.

In the end, I figured out that the key was the order in which I joined the tables. Once I got that down, the rest pretty much fell into place. I tend to sort of over-analyze and over-think things like this, and that’s one of my biggest problems.

6

@cydewaze glad that you figure out your problem :smile: