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

	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