The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

database

Here's my problem in designing a database.

An example:

A teacher can teach zero or more subjects. Now each teacher has different subjects, but some of them will likely have the same name. So I can opt by two diferent designs (at least)

Simplified:
[Subject]
 code, title, teacher

[Teacher]
 code, name

This is a simple design that basicly solves the problem. What raises from here is that some records in the Subject table will be duplicated (like there will seveeral subjects with title=physics].

Another:
[Subject]
 code, title

[Teacher]
 code, name

[SubjectTeacher]
 subject_code, teacher_code

This solves the subject title duplication problem but now the program needs more complex sql.

So what's best?
Djava
Friday, May 18, 2007
 
 
>What raises from here is that some records in the Subject table will be duplicated (like there will seveeral subjects with title=physics].

The second one (1NF)
Radu Chindris Send private email
Friday, May 18, 2007
 
 
Agreed. Second choice. And that's not too complex SQL. Granted, it is an example and the joins can get pretty complicated with 3rd or higher normal form databases.

If writing the queries is difficult for the same person(s) who designed the database, I'd be scared about the data integrity of the application...

Normalization (at least to 3NF) is a good thing.
anomalous
Friday, May 18, 2007
 
 
This is the most common textbook example used to explain normalization in introductory database courses.  Is the OP asking for help reading the textbook, or just posing as a child for some obscure rhetorical purpose?
Feeding the Troll?
Friday, May 18, 2007
 
 
... and you might need a date range in that subjectteacher table. Stuff changes.
David Aldridge Send private email
Friday, May 18, 2007
 
 
"Agreed. Second choice. And that's not too complex SQL. Granted, it is an example and the joins can get pretty complicated with 3rd or higher normal form databases."

Well it's not "complex SQL" is "less simple SQL".

In my program the second example implies that a third table has more information so the join will be 3 using table.
Djava
Friday, May 18, 2007
 
 
Did you read my whole post, or just find something to take offence to?
anomalous
Monday, May 21, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz