From: MC

To: Editor

 

I'm sorry to bother you all but I have a technical question about design which may well be covered somewhere in the texts, but seems to hiding very well from me. Since this site is the best site around for solid information with good logical foundations, I thought it best to ask you rather than get some ill-formed opinions, which seem rife in the computing world.

 

When implementing a M:N relationship, I have created a link relation, which joins the two tables by taking their primary keys as foreign keys and then using the combined key as the primary key in the new relation. To illustrate, I have a STUDENT and ASSESSMENT relation and they are joined by a SUBMISSION relation.

 

STUDENT(*sid,name,...)

ASSESSMENT( *assid,Name,...)

SUBMISSION( *sid, *assid,...)

 

My problem is I also have a relation FILE, which I need to relate to the SUBMISSION relation and I am not sure of the best way to proceed.

 

Should I make a SUBID primary key for the SUBMISSION table and enforce a uniqueness constraint on the (ASSID,SID), allowing me to have a single FK in the FILE relation, or should I just put in ASSID and SID as FKs in the FILE relation?

 

I suppose either way would work but I am aware of my shortcomings in database design and would like to do it properly.

 

I would also like to take this opportunity to commend you on your fine work. While I must admit I do sometimes struggle to fully follow when you get technical in your articles, I do consider the effort well worth it, and hope you continue the good work in the future.

 

I apologize in advance if my naivety and ignorance places me squarely with the norm, and I thank you in advance for any help you have to offer.

 

 

From: Fabian Pascal

To: MC

 

Your question is a good example of why it is not possible to provide specific design advice remotely via email. I am saying this not to criticize you, but to teach you.

Since I do not know what the FILE relation represents and how it relates to SUBMISSION, how can I tell you how to design this?

The point is this: without information about the meaning of all the tables -- that is, what it is that they represent in the real world (the predicates), it is impossible to know how to design the database.

While you are trying to learn, you do it the wrong way. First learn what tables in a relational database mean -- what the predicates are -- then you will know how to design the tables. You are trying to figure out how to design tables without a good grasp of the predicates, which is why you don’t know how to proceed. Asking somebody else to tell you how doesn't solve the problem, because he does not know the predicates either. You got to learn to formulate the predicates (your conceptual model), and only then you will know how to design and you won't need to ask others.

 

Posted 04/11/03

 

 

 

[ABOUT] [QUOTES] [LINKS]