asked
Create Child Table
I have two tables in sql server 2012 as 'CropPurchase(Date,CropId,CustId,Qty,Price,Discount,Paid)' and 'CropSale(Date,CropId,VendorId,Qty,Price,Discount,Paid)' by joining transactions of these two tables i have to fetch an account on front end(C# app), naming 'CropAccount' and order its transaction by 'Date'. So Question is, that what should be the good practice:
1.To Create another table which will Contain mixed records of both the tables and fetch this table on front end
2.To fetch record directly from the two tables to front end and not creating a third table.(it can create problem while ordering by Date) help required please
It is better to query the tables directly and return data as a join between the two, instead of creating a third table that then contains duplicate data. If you show us what you have and where there is a problem with the data ordering, we can probably help.
Duplicate data goes against the whole principle of data normalisation, and opens you up to a nice big range of data synchronisation issues.
Thanks @Kev. One More Question, How is that to maintain only one table i.e CropAccount instead of two accounts i.e CropPurchase and CropSale
Yes you could merge the 2 types, Purchase and Sales into one Account table, as a replacement, and not as an additional table - whether you do that or not depends on how similar the 2 entities are. What you have is a supertype/subtype design - maybe you should look into the different ways of approaching this.
14 People are following this question.
