Where do I put my Database relations... at DATABASE level or in PROGRAM LEVEL?

[mesut]

Mybe it's a stupid question but :
I'm starting to learn VB.NET. I have some basic skills. I'm from
Mainframe world and I have same question to make my DATABASES (tables
and relation)
I would like to make ask my question by example. Let's say I'm a
sales company I'm selling products to customers. Okay?
I have these tables (databases) and fields and I have these questions.

I have created these tables in VB.NET Database Connections IDE
interface. These 3 files and fields are visually created and I have
also entered some test data.
This is the structure: 1 Customer can have Multiple order, 1
OrderHeader can have Multiple orderDetaillines.
So I only store in OrderHeader the Main things like customer, Order
remark. Everything which is related to product I store it in
OrderDetailTalbe like product, price etc.

MY Intention: I don't want to set my RELEATIONS on DATABASE level,
but in PROGRAM LEVEL, I mean I don't want to visualize to draw a line
from Customer table to Order Header and from Order Header to Order
Detail.........
In Mainframe world we don't put DATABASE relations in DATABASE level,
but in PROGRAM level. ......
My QUESTION: IS this allowed by VB.NET? Can I build an application
like this? Is this a correct way to do? Or do I need to make a Database
Relation DIAGRAM?

WHAT is your idea about it as Experienced VB.NET programmer?

1) CustomerTable
==============
- Customer ID
- Customer Name
- Customer Address
- Customer City
- Customer Country
- Customer Payment term

2) OrderHeaderTable
=================
- Order Number
- Order Type
- Order line
- Customer ID
- OrderRemark
-

3) OrderDetailTable
===============
- Order Number
- Order Type
- Order line
- Customer ID
- Product
- Sales price
- Extented price


Thanks
Mesut

 

[TB]

I am not really an expert on this but I think you obtain everything your
want by way of your SQL statement.

For example if you want to display a list of products of an order by a
particular customer, and assuming that you have created three tables
(Customers, Orders and Orderlines), your SQL statement would be something
like
SELECT c.Firstname, c.Lastname, l.productID, l.productname FROM Customers as
c
INNER JOIN Orders AS o ON c.CustomerID = o.CustomerID
INNER JOIN Orderlines AS l ON rderID = l.OrderID
WHERE c.CustomerID = 23 AND rderID = 10
ORDER BY l.ProductID

The values 23 and 10 are just example.

Then you can build your code to display the resulting output.

Cheers

TB

 

[Patrice]

Just to make sure, the "diagram" establish behind the scene the referential
integrity (using FOREIGN KEY constraints). You can do it in script if you
prefer.

My personal preference would be to always have referential integrity defined
in the DB. It will prevent an inconsistent database if your program by
mistake doesn't enforce this correctly plus it will prevent to introduce
inconsistencies from whatever else could access the database or when it
evolves etc...

That said if you have the same compelling reason than on mainframes for not
doing so, you are anyway never forced to define this in your DB...

 

[Garth Wells]

... Or do I need to make a Database Relation DIAGRAM?<<

If you create your tables properly using FOREIGN KEY constraints the
software (e.g., visio, enterprise manager, ERwin, Mgmt Studio) will be
able to automatically create a diagram that shows table relations.

This is an abreviated example of the proper way to create the tables
in SQL Server 2005.

-- Code
CREATE TABLE Customers
(
CU_ID int PRIMARY KEY,
CU_Name varchar(30) NOT NULL,
CU_Address varchar(50) NOT NULL,
CU_City varchar(30) NOT NULL,
CU_Country varchar(30) NOT NULL,
CU_PaymenTerm varchar(30) NOT NULL
);

CREATE TABLE Products
(
PR_ID int PRIMARY KEY,
PR_Description varchar(100) NOT NULL,
PR_Price smallmoney NOT NULL,
PR_Qty smallint NOT NULL
);

CREATE TABLE Orders
(
OR_ID int PRIMARY KEY,
CU_ID int FOREIGN KEY REFERENCES Customers(CU_ID),
OR_Type varchar(30) NOT NULL,
OR_Description varchar(1000) NULL
);

CREATE TABLE OrderDetails
(
OD_ID tinyint PRIMARY KEY,
OR_ID int FOREIGN KEY REFERENCES Orders(OR_ID),
PR_ID int FOREIGN KEY REFERENCES Products(PR_ID),
PR_Price smallmoney NOT NULL,
OD_Qty smallint NOT NULL
);
-- End of Code

IMO, it is much better to use SQL instead of the GUI to create
your tables. The main benefit, at least for me, is it forces me to
think through the design. Plus, I can recreate the entire database
easily when a design change is made.


Garth
www.SQLBook.com

 

[mesut]

Thank you all, all information was really useful

If I correct understand I can better use constrains (database
relations) but it's not obligated by the system (Visual
Studio.Net)...

TB : Thank you for the SQL sample, that was also the my idea to use
it.

Patrice: I think you've got the right words... I don't want to
define Foreign Key Constrains. In Mainframe we just read the
OrderHeader and then we read OrderDetail in the program. But there is
no constrains (links with tables). But I agree then you can have
OrderDetail Record information without OrderHeader Record, but this is
not my issue for the moment...

Garth: That sample is also very good. I'm using Visual Studio.NET
Express Edtion + SQL 2005 Express... Where do you type your code to
create these tables?

sorry guys here is my last queston:
- When a Field e.g. Payment term is NOT NULL means : when the primary
field is filled in (record created) the NOT NULL fields are obligated
to fill in. You should have filled in a value CORRECT?

regards,

Mesut

 

[TB]

That´s right.

Whenever a new row is added, something has to be put into a Not Null column,
for example a 0 for a numeric column or an empty space for a varchar column.

TB

 

[Garth Wells]

Garth: That sample is also very good. I'm using Visual Studio.NET

Express Edtion + SQL 2005 Express... Where do you type your code to
create these tables?

Go to Microsoft's website and look for: Microsoft SQL Server
Management Studio Express. I believe there are other free tools
available, but cannot recommend any because I haven't used them.

One more thing...you were asking about NOT NULL...If possible,
I like to define the domain of a column via a CHECK constraint when
creating a table. This accomplishes two goals:

1. It prohibits invalid data from being inserted into the column, and
2. It allows other developers to know more about the data more
quickly.

It's one thing to know a column can accept up to thirty characters
(e.g., varchar(30)), but it really helps to know that the only valid entries
are x, y and z. And, of course, it is also helpful to define DEFAULT
constraints as well.

The following table definition is from my current project...it contains
examples of these types of constraints.


CREATE TABLE WorkItems
(
WI_ID int PRIMARY KEY,
WI_Type varchar(8) NOT NULL CHECK (WI_Type IN ('Issue','Project','Task')),
WI_Title varchar(100) NULL,
WI_Priority varchar(10) NULL CHECK (WI_Priority IN ('(1) High','(2)
Normal','(3) Low')),
WS_ID tinyint FOREIGN KEY REFERENCES WorkItemStatuses(WS_ID),
WI_PercentComplete varchar(3) NULL,
WI_AssignedTo int NULL REFERENCES Employees(EM_ID),-- Holds ProjectLeader for
Projects
WI_Description varchar(1000) NULL, -- Holds Comment for Issues
WI_StartDate datetime NULL,
WI_DueDate datetime NULL,
WI_CreatedDate datetime NOT NULL DEFAULT GETDATE(),
WI_CreatedBy int NOT NULL REFERENCES Employees(EM_ID),
WI_LastModifiedDate datetime NULL,
WI_LastModifiedBy int NULL REFERENCES Employees(EM_ID),
WI_Caller int NULL REFERENCES Employees(EM_ID),
BR_ID varchar(15) NULL REFERENCES Branches(BR_ID),
WC_ID tinyint NULL FOREIGN KEY REFERENCES WorkItemCategories(WC_ID),
WI_EmployeeWithIssuePhoneNumber varchar(15) NULL,
);



Garth
www.sqlbook.com

 

[Garth Wells]

One last thing...you should post your database design/programming
related questions in microsoft.public.sqlserver.programming. There
are a lot of very smart, helpful people that monitor that newsgroup
and you will get excellent answers and tips. At least that's always
been my experience.

 

[mesut]

Thanks guys. I have learned more today. I will follow your advices.

regards,

Mesut