Extract Normal Form from XML

Question

<Historic>
-----<ReturnData Year="2010">
----- -----<ReturnHistory>
----- -----<Return StoreDate="2010-07-31" Type="1">
----- -----<EndDate>2010-01-31</EndDate>
----- -----<ReturnDetail TimePeriod="1">
----- ----- -----<Value>2.83</Value>
----- ----- </ReturnDetail>
----- -----</Return>
----- </ReturnData>
-----<ReturnData Year="2010">
----- -----<ReturnHistory>
----- -----<Return StoreDate="2010-07-31" Type="1">
----- -----<EndDate>2010-01-31</EndDate>
----- -----<ReturnDetail TimePeriod="2">
----- ----- -----<Value>1.83</Value>
----- ----- </ReturnDetail>
----- -----</Return>
----- </ReturnData>
<Historic>

I'm storing this in a table as follows:

Table Name = Historical

Columns

StockId : int  
StockCode: string  
Year:int  
StoreDate: datetime  
EndDate: datetime  
ReturnTypeId: int 
TimePeriodId: int  

Is my table in the third normal form?

Would you recommend this structure or should I create corresponding tables for each parent node i.e. normalise it further?

Answer 1

Just from the structure, there's no clear way to answer this - whether a table is in 3NF or not is largely dependant on the meaning and the "semantics" of the data stored in that table.

I'm just guessing here: the Year column is most likely dependent on the StoreDate (or EndDate) column - right?

Also, more guessing: StockCode and StockId are dependant on one another - this also violates 3NF - try to put your stock information into a separate table:

Stock Information

StockId    INT
StockCode  STRING

and then keep only the StockId in your actual table and add a reference from your table to StockInformation on the StockId column.

If you remove those two issues, there's a good chance your table could now be in 3NF - but again, that depends on that data stored and what that really means - cannot accurately check for 3NF just based on the structure of the fields.