I don't even know what this is called to search for it

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

I don't even know what this is called to search for it

Post by leenoble_uk »

I'm going to have to dip straight in with real world examples here. It's a theory issue but I don't know what these systems are called so I'll describe it thusly:

I have an SQL table of solicitors details.
There is a column called work_category which currently has in text things like "Wills & Probate", "Trusts", "Conveyancing" etc.

This table needs to be searched using drop down menus to list these categories. Now I think it would make more sense for extensibility reasons to have the ability to keep track of more than one work category for each solicitor. For a start I'm keeping all the categories in an array so I can deal with simple numbers instead of text as it's a little more secure (sql injection and all that)

Code: Select all

<?php
$subSectorArray = array("Any"
						,"Wills & Trusts"
						,"Probate"
						,"Conveyancing"
						,"Matrimonial"
						,"Corporate"
						,"Other"
						);
?>
So now instead of the column containing text it could contain a "1" for Wills and Trusts, a "2" for Probate etc.

Next thing, I want to have multiple sub categories, but since I want it to be extensible I want to keep using one column.

I could use a text field and put multiple numbers in with a delimeter like 2|4|5, then split on the delimeter and see if the array contains "3" for conveyancing.

Thinking about the unix permission system I thought of using some binary type of coding, so for the above entry (2|4|5) I would enter 010110 instead or perhaps the decimal equivalent "22" could be used.

But what happens if another category is added, or if the category list is dynamic? How do I make this system extensible for any number of categories? And what's this system called so I can do a search on it?

Or is there something simple I'm overlooking?

Having written that all out I'm now inclined towards the delimeter option. If I put a pipe in at the start and end I could just put a regex search in my sql for a pipe followed by the array_key and a further pipe which would be quite simple to do. But would I be missing out some vital new knowledge if I used the permissions type scheme?

Help? Comments? Suggestions?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Essential reading on database design.

Never store more than one bit of data in a field - breaks one of the normal forms (forget which exactly).

You can denormalise a db for performance reasons but that's probably a decision to make after producing a properly normalised version. A fully normalised db is much more flexible at the design stage - and later if you have to add new features to an app.

feyd | made linkie workie ;)
User avatar
lazy_yogi
Forum Contributor
Posts: 243
Joined: Fri Jan 24, 2003 3:27 am

Post by lazy_yogi »

To decode what McGruff said:

Since solicitors-to-category is a many-to-many relationship, you need another table

Code: Select all

solicitor
---------
id  name   age
1   john   25
2   jill   35

Code: Select all

category
----------
id  name
1   probate
2   conveyancing
3   other

Code: Select all

solicitor_category (think of a better name tho =P)
---------------------------------------------------------
solicitor category
1         1
1         2
2         2
This way, to search for all solicitors in conveyancing:

Code: Select all

SELECT
    s.*
FROM
    solicitor s, solicitor_cagetogory sc, category c
WHERE
    c.name = 'conveyancing' AND  // use category table
    c.id = sc.category AND       // join the solicitor_category table 
    sc.solicotor = s.id          // join the solicitor table
That way you can add solicitors, categories, and multiple categories for each solicitor and search for categries of a solicitor or solicitors of a categeory

Regards,
Eli
leenoble_uk
Forum Contributor
Posts: 108
Joined: Fri May 03, 2002 10:33 am
Location: Cheshire
Contact:

Post by leenoble_uk »

Ok, thanks.
I understand what you're saying. I was thrown because for now I'm just keeping the list of categories in a hardcoded array since it won't be changing anytime soon and since there isn't going to be any need for a client content management system.

I since looked around and found references to bitwise operators. I'm not saying I'm going to go that route but IF the list of categories wasn't potentially dynamic would this not be a convenient method to store all that information in one column? if I knew how to get it to work of course.

Guess I'll come back and ask again when I find myself with that exact scenario.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

leenoble_uk wrote:... but IF the list of categories wasn't potentially dynamic would this not be a convenient method to store all that information in one column?
Don't do it. The O'Reilly link explains all.
Post Reply