Bad Design?

Table A
[ID] [SalesmanEmail] [SalesmanName]
Table B
[ID] [QuoteNo] [SalesmanID]
Table C
[ID] [Product] [QuoteNo]

Program A creates the records for Table C. In the process it reads from Table A and Table B.

Program B reads record from Table C. Program B now needs [SalesmanEmail] field.

The suggestion is to add [SalesmanEmail] to Table C. Is this good or bad design?Select TableA.SalesmanEmail From TableA Where TableA.ID In (Select Distinct TableB.SalesmanID From TableB Where TableB.ID = whatever);|||That's how I suggested we do it, however the "senior" programmer was the one who asked me to write out SalesmanEmail directly into Table C.

I tried to explain to him that if there were ever any modifications to SalesmanEmail in Table A or to SalesManID in Table B, every record I wrote in Table C would now be invalid because we wrote it to a column instead of doing a lookup, but he still didn't get what I meant. And his method also wastes DB space by storing the same field in multiple tables.|||The suggestion to put the salesman's email address into Table C violates the Second Normal Form of database normalization. It belongs solely in Table A.

Terri|||:: Table C violates the Second Normal Form of database normalization.

Is that a misdeameanor or felony? or does the SQL cop just write you a ticket?

j/k. :)

Yeah, I just gave a query example how you don't need to add a new field if it's already in another.|||But he would probably be more comforted to see:

SELECT
 TableC.Product,
 TableC.QuoteNo,
 TableA.SalesmanName,
 TableA.SalesmanEmail
FROM
 TableC
INNER JOIN
 TableB ON TableB.QuoteNo = TableC.QuoteNo
INNER JOIN
 TableA ON TableA.SalesmanID = TableB.SalesmanID

;-)

Terri
PS - I think the penalty is pocket protector removal