Is it good to save Keywords stored by users in a new DB schema

database-designperformanceschema

My question is more from the schema design and of further performance of the DB.

I'm designing a DB schema which will store strings primarily. Actually, users work on these strings, as normal mortals, in macro-enabled Excel workbooks and there, they have one column specifically designed to store keywords so they can find words within a second column quickly.

Some of these words are obviously deduced. For example, if column 1 is the keywords column and column 2 is the text column, column 2 could say "information required for feature description" and, its keywords in column 1 would be "information", "feature" and "description".

So far so good, common sense, but there are texts that have, for example, "N", "S", "W", "E", as the four points of a compass but in the keyword column, instead of having "North", "South" and so on, the keyword for all is just "compass".

We could ask for a keyword change but it would be a little troublesome, so my question is, is it worth saving these keywords in the schema, whether there's a table for them or not, or is it better to get the keyword as the result of a query from the texts stored?

We know working with strings is difficult, would these affect performance in the future?

BTW, if it helps, I'm thinking of using PostgreSQL.

Best Answer

I would have 2 columns: one for the description and one for the keyword. I would probably use MyIsam (depending on which Mysql version you're using) and use Mysql FullText search features. Innodb supports it now. I would test with both and see which ones is most performant.

Related Solutions

Sql-server – Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view

I might be misunderstanding your question. Are you asking if given the following tables and views:

CREATE TABLE dbo.Foo
(
    [col1_abc] INT
    , [col2_def] INT
)
GO

CREATE TABLE dbo.Bar
(
    [col1_ghi] INT
    , [col2_jkl] INT
)
GO

CREATE VIEW dbo.vwFoo
AS
SELECT
    [col1_abc] AS col1
    , [col2_def] AS col2
FROM
    dbo.Foo 
GO

CREATE VIEW dbo.vwBar
AS
SELECT
    [col1_ghi] AS col1
    , [col2_jki] AS col2
FROM
    dbo.Bar 
GO

Will this:

SELECT  
    f.col1
    , f.col2 
    , b.col2
FROM
    dbo.vwFoo f
INNER JOIN
    dbo.vwBar b
ON  b.col1 = f.col1

perform comparably to:

SELECT
    f.[col1_abc]
    , f.[col2_def] 
    , b.[col2_jki]
FROM
    dbo.Foo b
INNER JOIN
    dbo.Bar b
ON  b.[col1_ghi] = f.[col1_abc]

If that's the question the answer is yes, the performance will be identical.

In SQL2000, the optimiser sometimes struggled with deeply nested complex views joining to each other but not with simple scenarios such as this. I've not encountered the problem so much in 2005+.

Mysql – Rows with a variable number of columns

Edited after a couple of comments by the question asker!:

Better idea for your design: new sample schema

(If you're not familar with these diagrams, that's seven tables, with foreign key links as pictured [the three _Link tables all have two foreign keys referencing the data tables]. It probably looks like I'm overcomplicating things, but trust me, "tokenise once and store in the database" is so much more efficient than "tokenise data each time you retrieve it".)

Sample data, using a simple tweet (ignoring Users for now, it's the same concept though): "test tweet! #howdoesthiswork #newbie"

      Tweet
TweetID   WordCount
   1         2

Tweet_Word_Link
TweetID   WordID
   1         1
   1         2

      Word
WordID    Word
   1      test
   2      tweet

Tweet_Hashtag_Link
TweetID  HashtagID
   1         1
   1         2

      Hashtag
HashtagID  Hashtag
    1      #howdoesthiswork
    2      #newbie

This allows you to easily get all the users linked in a tweet:

SELECT U.UserName
FROM  User U
        INNER JOIN
      Tweet_User_Link TUL ON U.UserID = TUL.UserID
        INNER JOIN
      Tweet T ON TUL.TweetID = T.TweetID
WHERE T.TweetID = <your ID here>

(change table names as appropriate for Hashtags and Words)

Or, slightly more complicated, get the top 10 words for all tweets mentioning a specific hashtag:

SELECT TOP 10 W.Word, COUNT(*) AS WordCount
FROM  Word W
        INNER JOIN
      Tweet_Word_Link TWL ON TWL.WordID = W.WordID
        INNER JOIN
      Tweet T ON TWL.TweetID = T.TweetID
        INNER JOIN
      Tweet_Hashtag_Link THL ON THL.TweetID = T.TweetID
        INNER JOIN
      Hashtag H ON H.HashtagID = THL.HashtagID
WHERE H.Hashtag = <your hashtag here>
GROUP By W.Word

(I've used ANSI SQL syntax, I'm not 100% sure if MySQL supports all of ANSI SQL - but it should be close enough.)

Best Answer

Related Solutions

Sql-server – Are there significant performance differences when linking to a simple data view, rather than a data table, in a simple data view

Mysql – Rows with a variable number of columns

Related Question