Database Design Help

Question

Hello; Hoping someone can help with a design issue. I have to learn C# for work. Part of my work will be calling information from a database. Not sure of the type. I thought that it would be good practice to design an application and integrate it with a Jet database. I understand that much of the work will allow me to migrate to SQL or mySQL if needed. So I am building a grocery shopping list as an example. My question is regarding how to create the tables. I have categories of FrozenFruit FreshFruit and CannedFruit. I realize that I will be duplicating some of the input (Peaches) but I will need the lists within C# for auto populating or suggestive typing in form txtFields. This is where it gets tricky for me. Should I go with a Brand table(Cambell's Progresso) listing Soup (Clam Chowder Chicken Noodle) with a size and price in columns 3 and 4? This will leave out an auto populating Soup list because size and price may differ by brand (10.25 oz Chicken Noodle vs. 10.75 oz Chicken Noodle)? I am a bit perplexed and any advice you can provide would be greatly appreciated. My skill level with database design is below novice. Idiot would be a better descriptive term. I am basically typing my tables into Excel and importing them so that I can have the wizard automatically fill in the NoDuplicates and Key assignment. Thank you in advance for any help. All responses and opinions will be greatly appreciated and helpful. God Bless Frank Pytel

Bechir Bejaoui · Answer

First there is no relation between C# and database designing concepts designing data bases is creating data tables that have data relations between them in a given logical context and C# is a language for building applications that may be consume data from data bases. To know how data bases are designed I think that you have to focus on relational model first and study it carefully. http://en.wikipedia.org/wiki/Relational_model you can google Relational model for more info I give you some important details concerning data base reguardless of their types What one should respect when desinging a database is the three normal formats 1. The first normal format http://en.wikipedia.org/wiki/1NF 2. The second normal format http://en.wikipedia.org/wiki/2NF 3. The third normal format http://en.wikipedia.org/wiki/Third_normal_form And the rest is are details I mean SQL language because it parcially differs from a data base system type to other But in general there are three levels concerning SQL reguardless of the data base type 1. The DDL data definition language http://en.wikipedia.org/wiki/Data_Definition_Language 2. The DML data manipulation language http://en.wikipedia.org/wiki/SQL 3. DCL Data control language http://www.georgehernandez.com/h/xComputers/Databases/SQL/DCL.asp The first one is used for creating data base data tables modifying them and erase them The second is for requesting the data bases and the third is for controling the data base users form the administator by alllowing or restricting some defined tasks according to some users

Frank Pytel · Answer

Vipin; Wow! Thanks for the detailed response. When I got the notification I thought it was a newsletter and did not pay attention to it. Thank you. I sincerely appreciate it. I guess I wasn't very clear on my thoughts. I am looking more for experience. What have you done in the past? I am trying to decide on the layout. What will convert best in C# from a database. Maybe that's what you are trying to show me and I am not getting it. As I stated I am a complete novice to C#. I will look at it some more and maybe probably it will jump out and smack me. Thanks Vipin. God Bless Frank Pytel

vipin sreekumari · Answer

Hi Frank I have come up a minimum table design where in you can acheive the intended result. Please have a look into it and let me know if you have any questions. Best Regards Vipin Composite Primary key Primary Key 1. Product types - tblProductType tblProductType Field Name Datatype Field Length Comments lProductType_sk number can be an auto increment number sProductType_cd text 5 product type code sProductType_desc text 50 product description 2. Product Information - tblProduct tblProduct Field Name Datatype Field Length Comments lProduct_sk number can be an auto increment number sProduct_id text 5 product type code sProduct_desc text 50 product description sProductType_cd text 5 Foreign Key of tblProductType mUnitPrice_amt currency 3. Transaction Data - tblOrder tblOrder Field Name Datatype Field Length Comments lOrder_sk number can be an auto increment number sOrder_id text 5 the order id to be displayed in the screen dtLastUpdate_dt datetime Last update date including time sLastUpdate_id text 50 The user id tblOrderDetails Field Name Datatype Field Length Comments lOrder_sk number The order sk from tblOrder lOrderDetail_sk number can be an auto increment number lProduct_sk number Product sk from tblProduct lNoofUnits int Number of units of the selected product