Organizing Sql Database

[dyanc]

This question is probably outside the realm of this forum but I was hoping that someone might take pity on me! We have a large database of media outlets with names, contact info, etc. In a “category” column, we enter the focus of each contact – News, Politics, Health, Family, etc. (info is entered using shorter forms, i.e. N, POL, FAM, HL). We have some cases where one person will fall into more than one category, and I've been told that I can enter more than one category with a comma between (i.e., N, POL, HL). Question: Is there a limit to the number of categories that could be separated by a comma?

It has been suggested that we set up a separate column for each category (about 20), and then place a “Y” in each column that is appropriate for the contact. I think it would be unwieldy to have to look at 20 columns of categories before I even get to see my other information. Does anyone have a suggestion as to how others may have set up a similar database?

My knowledge of SQL and databases is VERY basic – I know enough code to write the SELECT statements that I need! Thanks.

[Larry Woods]

'dyanc' wrote:

This question is probably outside the realm of this forum but I was hoping that someone might take pity on me! We have a large database of media outlets with names, contact info, etc. In a “category” column, we enter the focus of each contact – News, Politics, Health, Family, etc. (info is entered using shorter forms, i.e. N, POL, FAM, HL). We have some cases where one person will fall into more than one category, and I've been told that I can enter more than one category with a comma between (i.e., N, POL, HL). Question: Is there a limit to the number of categories that could be separated by a comma?

It has been suggested that we set up a separate column for each category (about 20), and then place a “Y” in each column that is appropriate for the contact. I think it would be unwieldy to have to look at 20 columns of categories before I even get to see my other information. Does anyone have a suggestion as to how others may have set up a similar database?

My knowledge of SQL and databases is VERY basic – I know enough code to write the SELECT statements that I need! Thanks.

Dyanc:

First, if you are creating a column with a single category in it then you need to put a single category in it. Remember that as far as mySQL is concerned this is nothing but some text. If you choose to put multiple categories in a single field using commas to delete then you have created a string of characters. The point is that mySQL will NOT recognize this as being “multiple categories” but only as a string of text.

You could create multiple fields for categories but the “proper” way to do this is to create an additional table, say called “ownercategorylinks” that would have records that might look like “ownerID,category”. And, better yet, you should have a table of “categories” with a schema like “categoryID,category” and then the “ownercategorylinks” schema would look something like “ownerID,categoryID”. This way you can have unlimited owner/category combinations.

Hope this helps…

Larry Woods

[smine]

'dyanc' wrote:

… In a “category” column, we enter the focus of each contact – News, Politics, Health, Family, etc. (info is entered using shorter forms, i.e. N, POL, FAM, HL). We have some cases where one person will fall into more than one category, and I've been told that I can enter more than one category with a comma between (i.e., N, POL, HL). …

if the categories are well-defined and don't change often, you could use the SET datatype for the column. you can search for categories using something like “WHERE find_in_set('POL',category)”. yes, you can have multiple values in the column, all the way up to every category.

i'm not saying this is ideal–some people hate using sets–but i think it would work and might be sufficient for your purposes. MySQL doc for SET datatype can be found here.

[peterlaursen]

You can check this: http://kb.askmonty.org/en/dynamic-columns

'dynamic columns' will be available in next MariaDB release. But with SQLyog GRID you will just have a single column (a BLOB I think) and CREATE/ALTER TABLE will need to be done by writing SQL, .

[Author]

Posts