Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 a table with a unique field

Author  Topic 

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 17:50:53
Hello,

I would like to store some frequently asked questions in a database.
As those questions/answers could be translated in several languages, I was thinking of using two tables :

The first table would contain a unique field : the key wich is an id for each faq.

The second table would contain those fields :
FAQ_ID : foreign key
LANG : language
QUESTION : the question translated in the good language.
ANSWER : the answer translated in the good language.

What I find embarrassing, is that first table just contains a unique field : the id. But it allows :
- to have a table in which a line correspond to an object. It is convenient for object relational mapping.
- it is more evolutionary. If, in the future, I have to add some statistics that concerns a faq, I will have the possibility to store them in the first table, avoiding in this way to duplicate data.

But, one more time, I find a bit embarrassing having a table with a unique field...

What is your opinion about that ?

Thank you in advance for any suggestion.

mathmax

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 18:01:08
I dont think you need the first table with the single column at all. The same column is present in the second table anyway. You *can* have tables with single columns but it would be redundant here.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 18:33:17
yes but for object relational mapping, I need that one object correspond to one line in the database...
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 19:07:12
quote:
Originally posted by mathmax

yes but for object relational mapping, I need that one object correspond to one line in the database...


can you explain "object relational mapping"

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 19:32:05
I consume the data with .net. So I have to fill the objects of my .net application with the data of the database. For that, I map each table to a class, each field to an object property. Each line in the parent table represents an instance of the mapped class. That's why I should have a parent table in which one line correspond to on object of the business logic layer.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 19:37:23
Cant the same line in the second table be mapped to the class? Its still a PK in the second table right?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 19:43:56
What is a PK ?
In the second table, I've one line per language translation so several lines correspond to a single "faq object".
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 19:48:43
Looks like there is some ambiguity. PK = Primary Key. If there can be several lines does that mean the FAQ_ID will be repeated? If it does get repeated then how can you map it to the parent table without a master-child relation?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 19:55:34
The FAQ_ID is repeated and it is a master child relation between the two tables. In the second table the PK is set on two fields (FAQ_ID + LANG).
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 21:41:37
And what will your parent table comprise of?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-10 : 23:01:36
My parent table is the first table that contains just one field the id. It is the table that will be map to the class.
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-10 : 23:47:52
Can you provide some sample data from both tables. I dont understand how you are establishing the PK-FK relation here. Where is the mapping from parent-child records? the parent table or the child table?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 2007-07-11 : 03:23:03
You are mixing your development techniques and that can be hard but personally I really hate that one-class-equals-one-table approach. OO & RDBMS don't always map that well - there are often things that are implementation details in one that the other can ignore. Consider an API approach where your database provides what you want for a given operation. From here you will see that you do not need that first table unless you need to somehow group your questions.
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-11 : 09:51:18
Here is some sample data :
http://www.orkos.com/tests/FAQ.html

Here are the class mapped to the first table :
public class FAQ
{
private int _Id;
private Dictionary<Iso, string> _Question;
private Dictionary<Iso, string> _Answer;

public int Id
{
get { return _Id; }
set { _Id = value; }
}

public Dictionary<Iso, string> Question
{
get { return _Question; }
set { _Question = value; }
}

public Dictionary<Iso, string> Answer
{
get { return _Answer; }
set { _Answer = value; }
}
}

The properties Question and Answer will be mapped to the second table.
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-11 : 11:36:38
well it depends on how you'll use your app.
if you want for your master class to have access to all languages at the same time
then go with your approach.

if you need only one class per language at one time
then use one table where you put FAQ_ID and LANG as your PK because.

Both options ORM nicely.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-11 : 11:55:09
I dont even see a need for the parent table. You can do a DISTINCT FAQ_ID and get all those values at any time.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

spirit1
Cybernetic Yak Master

11752 Posts
Posted - 2007-07-11 : 12:15:18
the parent version come in hady when you have a parent class and it holds all of the languages in the list.
this way you don't have to query the db again if you want to change the language.

however since it's not very likely that one person would be changing the language it reads there probably
is no need for master-child structure.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-11 : 13:36:34
dinakar you give me an idea. Instead of creating a parent table, I could just add a view in my database (select distinct FAQ_ID). Then I map the class to the view. In that way I don't adapt the database structure for the object relational mapping needs. What do you think about this ?
Go to Top of Page

dinakar
Master Smack Fu Yak Hacker

2507 Posts
Posted - 2007-07-11 : 13:39:13
It might be ok. I am not familiar with how the OO maps to DB. It "sounds" a little risky. So I would advise caution.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Go to Top of Page

mathmax
Yak Posting Veteran

95 Posts
Posted - 2007-07-11 : 14:26:01
But it would be less efficient than working directly on a table...
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2007-07-11 : 14:35:49
You have not given us enough info to help us decide. What is a FAQ? It is a "set of questions and answers?" And do you have many different sets of questions/answers? For example, perhaps one FAQ per department, like "Accounting", "HR", "Finance", etc?

If so, then you should definitely have a FAQ table, that corresponds to a FAQ class, that stores all of the base information about each FAQ. Creation date, created by, modified, permissions, display order, active, status, notes, etc. Anything that relates directly to a FAQ. I am sure there is more than just "ID" that you can store.

As for your class definition, instead of what you have, I would suggest something like this:

(just an example, please excuse the public members):

public class FAQ_Question
{
public string Question;
public string Answer;
}

public class FAQ
(
public int ID;
public ArrayList<FAQ_Question> Questions; <-- just an example, use whatever collection class you want
)

Your questions and answers should not be stored in different dictionaries if they go together; they should all be in one object.

As for the multiple languages, it all depends. Is it important to know that the "Accounting" FAQ has been translated into 3 languages, and that they all relate to the "Accounting FAQ"? Or, is language just an attribute of the FAQ itself, just something to use as a filter when deciding which ones to display? Seems trivial, but this is very important to define.

If each FAQ should have a specific translation for each question, then each question should have a set of Question/Answer attributes per language. That gives a model like this:

(pk's start with *)

Languages (*Language_ID)
FAQ (*FAQ_ID)
FAQ_Questions(*Question_ID, FAQ_ID)
FAQ_QuestionsText (*QuestionID, *LanguageID, Question, Answer)

or maybe you just need this:

Languages (*Language_ID)
FAQ (*FAQ_ID)
FAQ_Questions (*QuestionID, FAQ_ID, LanguageID, Question, Answer)

or, if the language is just an attribute of the FAQ itself, then just this:

Languages (*Language_ID)
FAQ (*FAQ_ID, LanguageID)
FAQ_Questions (*QuestionID, FAQ_ID, Question, Answer)

what about a title or a description of each FAQ? Should that be stored in different languages as well?

It all depends on what you are modeling and what you need to store. It is impossible, and not a good idea at all, for us to try to give you a good model just by looking at what you've done and to guess what changes to make, without knowing the underlying needs of and the specifics of what you are trying to model. If you can explain, in plain but precise english, what you are modeling, then we can help you better. Before you can decide HOW to model anything, in SQL or in OOP, you need to clearly map out and describe exactly WHAT you are modeling.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site