Click here to Skip to main content
15,886,632 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I need some advice regarding a problem, I want to put Meta Tags for various web Pages in a single DB and Number of these Meta Tags can run upto 8. The Structure I'm showing below..

Tag Name Tag Value
--------- ----------
Title
KeyWords
Description
...

Now, for a single page, I may or may not provide any Meta Tag at all, Even if I provide Tag Name, I May not provide Tag Value. In any way, I can put Maximum 8 Tag Names with 8 Corresponding Tag Values. That means total no. of fields are 16.

Now how to design DB to store these values?
1. Define 16 fields? OR
2. Take 16 Values [Including Blank], Join them by a Special character like '‰' and store the whole construct in String format in a single Field?
Posted
Comments
Timberbird 25-May-11 9:11am    
Don't you think it would be better to change structure? Create two tables, TagName (Id, Name) and TagValue (Id, TagNameId, Value), joining them to find tag values for page in question. This way it's easier to add new tags and you won't store too much extra data

DBMS are relational and so should be your data model.

This is what I would do:
TABLE_WebPage
------------
integer id;        //primary auto increment key
varchar(50) name;  //maybe UNIQUE index on that one
varchar(300) url;
.
.
.

TABLE_PageMetaTag
------------------
integer id;        //primary auto increment key
integer pageId;    //foreign keyto TABLE_WebPage.id
varchar(10) name;  //maybe UNIQUE index on pageId & name
varchar(50) value;


Here a parameterized SQL statement that would fetch all meta names and value for one of your web pages:
SQL
SELECT m.name, m.value 
FROM TABLE_PageMetaTag m
WHERE m.pageID = @pageID


And if you wanted to know how many meta tags there are on each page do this:
SQL
SELECT p.name, COUNT(m.*) AS NumberOfTags
FROM TABLE_WebPAge p
    INNER JOIN TABLE_PageMetaTag m ON p.id = m.pageId
WHERE 
    NOT m.name IS NULL
    AND
    NOT m.value IS NULL
GROUP BY p.name


I hope this gives you an idea of what I would suggest (for a start).
Actually I'd go even further and also make a separate table for the tag names. Then TABLE_PageMetaTag would not contain the name of the tag itself, but rather a foreign key to an extra table which we might call TABLE_MetaTagName.

The advantage of such an approach is that there may be a change in the number of tags and this solution accommodates quite nicely to that. Only meta tags that contain a value need to be stored and if you take my additional approach with the names in a separate table you can also find out which web pages have which meta tags missing on them.

Reap the benefits of a truely relational data model!

-MRB
 
Share this answer
 
v3
Comments
fjdiewornncalwe 25-May-11 9:34am    
+5. An excellent, comprehensive answer. I was going to answer with a much simpler version of the same.
Sandeep Mewara 25-May-11 10:10am    
5++ from me. Great answer and explanation. :thumbsup:
thatraja 25-May-11 11:14am    
Fine answer Manfred.
Kim Togo 26-May-11 8:18am    
Good solution! 5.
MRB's explanation was really outstanding, problem was my selecting a vote for him, I mistakenly cast a wrong vote for him. Sorry MRB. Now I dont even know I can change my VOte Rating.

Everybody helped me a lot. Thanks to all.
 
Share this answer
 
Thanx MRB, I absolutely agree with your solution. But suppose, in my situation, I am left with these 2 options as stated above for each page..

1. Either define 16 New fields in the table along with other fields OR
2. Define a single field which can hold all the values in some specially joined field [by special chars which needs to be php:explode()-ed later to retrieve the info]

thanks
 
Share this answer
 
Comments
Timberbird 25-May-11 9:52am    
I think if tag set isn't going to change, you should use 16 fields (or 8 knowing that Field_1 is Title, Field_2 is Keywords etc... bad practice, but reduces field amount). This way there won't be additional processing in reading/writing tags, which could affect performance. Updating single tag is much easier as well
Manfred Rudolf Bihy 25-May-11 10:01am    
I agree with you Timerbird. 16 columns is indeed unnescessary as the column name can be used for the tag name. So all that is really needed are 8 additional columns where each of these columns corresponds to one dedicated meta tag.
Putting everything (including the names of the tags) into one field is not a good idea. It tends to lead to incoherent data states.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900