Gossamer Forum
Home : Products : Gossamer Links : Discussions :

Category structure

Quote Reply
Category structure
Hi all. I recently purchased LinksSQL. I have read all the help sections of this site. I have read the soon-to-be released manual cover to cover. I have done three online tutorials on relational databases. I am currently reading Hernandez's book on Relational Database design and implementation. I am still very much a novice.

I am going to ask a question that some of you will probably characterize as "dumb". If my question irritates you, please don't reply. If there's anyone out there who can give me a simple explanation of this or even point me in the right direction, I would appreciate it very much.

Here's an explanation of what I'm doing. I'm building a directory of travel-related links. The links can be categorized by type of trip, for example Bicycling, Hiking, etc. They can also be categorized by location, for example N.America, Europe, etc. I would like to display a main category page that has two sections, Type of Trip and Location. Visitors could click on a trip type like Bicycling, and be taken to a page of subcategories that include locations, and then click a location and get links. Or they could click on a location like Europe, and be taken to a page of subcategories that include trip types. It's one data base, but it has two ways to navigate through it.

The part that confuses me is how I set up the category structure without tons of duplication. I'm sure there must be a way to create each type of trip and each location as single categories, and then get LinksSQL to do the organizational part.

If anyone can take a few minutes to help me here, it would be much appreciated.

Thanks in advance,

Carl

Quote Reply
Re: Category structure In reply to
The short answer is no. And yes. <G>

(Alex/GT might have a way, since a lot of internal stuff in the modules is not yet documented...) If you have done that much reading, then you can see the problem.

Links is not built for a multi-to-multi relationship as would be necessary for this. The existing logic, and table structure doesn't allow it. I've been working around it for the image gallery mod, so that people can browse by category, but search by topic. I've had to re-think how data is stored, and go from there.

It might appear there is a M:M relationship between Links and Categories, but there is really a M:1::1:M relationship via the CatLinks table.

While the "data unit" is a Link, the organizational unit is a Category. Categories are handled in a top-down fashion, where each category knows it's parent, but not much else.

From this, you can see if you added another "parent" category, then altered the build routines to use it, and modified the search routines, you could do this.

The easiest way, would just be to have two category trees, and just add a link to both categories.

Duplicate your categories, which is the "structural" part of links, but just "virtually" add the link to multiple categories using the power of the CatLinks table.




PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Plugins:http://LinkSQL.com/plugin
Quote Reply
Re: Category structure In reply to
Add two columns to your Links table:

TypeID: (INT, Not Null, INDEX, Not Unique)
LocationID: (INT, Null, INDEX, Not Unique)

Then create two other tables called Location and Type....

Location

LocationID (INT, AUTO, PRIMARY)
Country (LONGTEXT, NOT NULL)
CountryAbbreviation (TEXT, NULL)

Type

TypeID (INT, AUTO, PRIMARY)
Type (TEXT, NOT NULL)

Then add records for each of these table via MySQLMan or through the admin.cgi script.

The reason that you should have these columns as foreign keys in the Links table is for normalization purposes. Yes, you could have each of the new columns be an ENUM field with the various values defined in the %db_select_fields, but the problem is that if you edit the values in hash, then you will have to constantly update those two columns.

Of course, if you are going to have a M:M connection between the Location and Links table, meaing that there can be MANY links that have MANY Location, then you would have to create two additional intersection tables:

LocationLink

LinkID (INT, INDEX, NOT NULL, NOT UNIQUE)
LocationID (INT, INDEX, NOT NULL, NOT UNIQUE)

TypeLinks

LinkID (INT, INDEX, NOT NULL, NOT UNIQUE)
TypeID (INT, INDEX, NOT NULL, NOT UNIQUE)

This would allow you to have MANY locations and types for links. Although in terms of management purposes, you would have to define foreign keys in these intersection tables and make sure that if links are deleted, that they are also deleted from the intersection table.

Basically, this mirrors the relationship between Category and Links via the CatLinks table.

You'll have to hack the admin.cgi a bit to automatically load values from the Location table that will the admin to select Countries -AND- also hack the add.cgi for users to be able to select a Type and Location.

Then you can also add these columns into your search form to allow users to search by TYPE or Location.

Now, if you want to have these links statically listed in sub-categories...you could simply create the following structure in the Category table:

Code:

Type of Trip (Bicycling - Camping - Hiking)
---Location (America, Europe)
---Sub-Location (State)
---Sub-Sub Location (City)


Although I would recommend having the columns available via the Links table, so that it will be easier for users to search for TYPE and/or LOCATION via the search form. It would be way to complex to have these just as CATEGORIES/SUBCATEGORIES within the Category table, since you would have to query the CatLinks table, which can be cumbersome in terms of CPU, Memory, RAM and also downloading results for end-users.

Regards,

Eliot Lee
Quote Reply
Re: Category structure In reply to
Eliot,

Exactly what I was trying to avoid ... ;) It's a lot more complicated than it seems. Small things, that won't survive upgrades, etc. I've created a monster trying to do that with the image gallery mod, granted, I want 3 ways to lay things out, but still....

It would be easier to add fields to the Link record, and modify search.cgi to present the links in different ways.

Lay out the site the way you want to present it, by location or by mode, then allow searching via the other fields.

Or, duplicate your category structure, and simply add the link to the "Location" and "mode" trees when it's added.

Both are far more portable, and actually much, much simpler in the long run.

PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Plugins:http://LinkSQL.com/plugin
Quote Reply
Re: Category structure In reply to
Well, how would you address M:M connections?? Without creating intersection tables??? It seems that you would be violating Normalization Rules. By simply adding the columns in the Links table (which I did suggest in my REPLY), then you would only be able to add links to ONE type and ONE location.

I would hope that GT would address different relationships/connections for different types of fields/columns in future releases.

Regards,

Eliot Lee
Quote Reply
Re: Category structure In reply to
To both of you, many, many thanks for taking the time to respond. It sounds like I've raised an interesting question, and even though I don't understand everything you've said, I do get the gist of it. Now I'll go away and think about how to deal with this. I'll probably revise my plan and keep it as simple as possible, given my level of expertise.

Thanks again,

Carl

Quote Reply
Re: Category structure In reply to
Maybe I'm missing something here, but can't that be done by adding links via the admin page already? Just select more than one category for your link to belong to and set the categories up in a logical way to accomplish this sort of browsing:

Home: Type: Bicycling, etc.
Home: Location: Georgia, etc.

When a person is on the home page, they click either Type or Location and are presented with the appropriate category links. You would have to make sure to always select two categories for each link added, one from Location and one from Type, but maybe a script could be written that makes sure links are always in at least two categories.

Seems like a simple answer, so it's probably wrong :-)

Bryan


Quote Reply
Re: Category structure In reply to
Well, in terms of normalization, it is better to store values in other tables. Now, depending on the connection, you can store the values from the other tables in the LINKS table as 1->M connection.

However, as we all know, the real world is not so simple...links may be associated with multiple types (like bicycling and hiking) and multiple countries (North America and South America)...For instance, tourism companies don't only offer packages in ONE country with only ONE type of excersion...it may include hiking, biking, and kayaking! This would be a M:M (Many to Many) connection, which calls for an intersection table between Location and Links AND Type and Links.

Thus, in terms of Normal Form Rules (NF), putting multiple values as an array in a column violates 1st Normal Form. You cannot store multiple values in one column.

I would suggest taking a Relational Database tutorial, pick up a book, or take a Database Modelling/Design class.

Regards,

Eliot Lee
Quote Reply
Re: Category structure In reply to
It sounds like what I suggested would work, but it's not a Normalized way of doing business in a relational database, is that right?

I'm interested in this because at some point I need to do pretty much the same thing for Photographers. They will need to be classified by Location and Type as well, since both ways of looking for them are necessary.

The only downside I can see is that by running an abnormal database I could run into problems down the road should I decide to port the data to another program besides Links.

Since LinksSQL allows for multiple entries in a single field, is there a problem if I always use Links for my database?

I'm very reluctant to modify the .cgi scripts because of the problems upgrading to newer versions once there are too many alterations to the code. So, adding more tables also presents it's own set of problems when dealing with Links, and it's really a question of which solution has fewer potential problems from the administrators point of view.

So, I guess my question is, what's the worst that can happen by violating the Normal Form Rules?

Bryan

Quote Reply
Re: Category structure In reply to
If you violate Normal Forms and Relational database tules, then your database is not really Relational...

The downside is that you have to do a lot of coding to get the system to work and also in terms of data processing, it is much better to use intersection tables and establish secondary indexes of concatnated keys (foreign keys from connected table)...like I showed above with the LocationLinks and TypeLinks table.

Also, the major problem with relying on the %db_select_fields config in the .pm files is that if you ever re-name the values in the hash, then you will have to update your WHOLE table to include that new value. It is better to use a FOREIGN KEY, which is unique and does not change...meaning that you can change the column value in the connected table all you want, without breaking the LINKS table.

Example....

You have the following in the %db_select_fields hash:

Colors => 'Blue,Purple,Yellow'

And you want to change Yellow to Red. You would have to run an update the Colors Column to change the Yellow value to Red. If you forget to change the values in that column, then your pages will not show the appropriate linke.

VERSUS editing the ColorName column Colors table, which only involves editing the column name.

Although if you delete a COLOR from the Colors table, you would have to identify the Colors column in the LINKS table as a foreign key from the Colors table, so that when you delete the Color in the Color table, the value in the Colors column in the LINKS table would be reset to 0.

To see a live example of this, go to:

http://anthrotech.com/.../?search_jobs_form=1

You'll see that I have a series of drop-down menus of which all are pulling values from other tables. Now, I have purposely made 1->M connections between the other tables and the JOBS table because I've found that employers tended to select all values to increase their chances of being viewed in the search results,

But this system could easily be changed to a M:M connection so that jobs can have many types and sub-fields.

Regards,

Eliot Lee
Quote Reply
Re: Category structure In reply to
Eliot,

The M:1:1:M relationship between Categories:CatLinks:Links is not a true M:M relationship.

One link in the Categories table is _NOT_ related to many links in the Links table. In fact, it's not related to _any_ links in the Links in the links table, and vice versa.

Looking at CatLinks, doesn't help much, since it's nothing more than a mapping between two other tables.

There are certainly advantages using a relationship table between two other tables, but that is still a 1:M or M:1 relationship between any two tables.

Anyway, every solution has compromises, and breaking rules of normalization often is done for performance reasons. A fully normal database may perform poorly, while one that is partially normalized performs extremely well.



PUGDOGŪ Enterprises, Inc.
FAQ:http://LinkSQL.com/FAQ
Plugins:http://LinkSQL.com/plugin