数据库中的额外字段过多 [英] Too many extra fields in Database

问题描述

我需要有关问题的建议，我想将各个网页的元标记放在一个数据库中，这些元标记的数量最多可以运行8个.结构如下所示.

标签名称标签值
--------- ----------
标题
关键字
说明
...

现在，对于单个页面，我可能根本不会提供任何元标记，即使我提供了标记名称"，也可能不会提供标记值".无论如何，我最多可以放置8个标签名称和8个相应的标签值.这意味着总数.的字段是16.

现在如何设计数据库来存储这些值?
1.定义16个字段?或
2.取16个值[包括空白]，并用特殊字符(如‰")将它们连接起来，然后将整个结构以String格式存储在单个Field中?

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?

推荐答案

DBMS是关系型的，因此应该是您的数据模型.

这就是我要做的:

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;

这是一条参数化的SQL语句，该语句将获取您的一个网页的所有元名称和值:

Here a parameterized SQL statement that would fetch all meta names and value for one of your web pages:

SELECT m.name, m.value 
FROM TABLE_PageMetaTag m
WHERE m.pageID = @pageID

如果您想知道每个页面上有多少个meta标签，请执行以下操作:

And if you wanted to know how many meta tags there are on each page do this:

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

我希望这会让您对我的建议有个初步的了解.
实际上，我会走得更远，并为标记名称创建一个单独的表.然后，TABLE_PageMetaTag将不包含标签本身的名称，而是包含额外表的外键，我们可以将其称为TABLE_MetaTagName.

这种方法的优点是标签数量可能会发生变化，并且该解决方案可以很好地适应这种情况.仅需要存储包含值的元标记，如果您采用我的其他方法在单独的表中使用名称，则还可以找出哪些网页上缺少哪些元标记.

收获真正关系数据模型的好处！

-MRB

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

MRB的解释确实非常出色，问题是我为他选择了一个选票，我错误地为他投了一个错误的选票.抱歉，MRB.现在我什至不知道我可以更改我的投票等级.

每个人都帮了我很多忙.感谢所有人.

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.

感谢MRB，我完全同意您的解决方案.但是，假设在我的情况下，我在上面为每页保留了上述两个选项..

1.在表中定义16个新字段以及其他字段OR
2.定义一个字段，该字段可以保存某些特殊连接的字段中的所有值[通过特殊字符，以后需要用php:explode()-ed来检索信息)

谢谢

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

这篇关于数据库中的额外字段过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！