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

Discussion in 'ASP .Net' started by mesut@edpnet.be, Mar 16, 2006.

  1. Guest

    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
     
    , Mar 16, 2006
    #1
    1. Advertising

  2. TB Guest

    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 o_OrderID = l.OrderID
    WHERE c.CustomerID = 23 AND o_OrderID = 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


    <> wrote in message
    news:...
    > 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, Mar 16, 2006
    #2
    1. Advertising

  3. Patrice Guest

    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...

    --
    Patrice

    <> a écrit dans le message de news:
    ...
    > 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
    >
     
    Patrice, Mar 16, 2006
    #3
  4. Garth Wells Guest

    >>... 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
     
    Garth Wells, Mar 16, 2006
    #4
  5. Guest

    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
     
    , Mar 16, 2006
    #5
  6. TB Guest

    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

    <> wrote in message
    news:...
    > 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, Mar 16, 2006
    #6
  7. Garth Wells Guest

    > 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, Mar 16, 2006
    #7
  8. Garth Wells Guest

    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.
     
    Garth Wells, Mar 16, 2006
    #8
  9. Guest

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

    regards,

    Mesut
     
    , Mar 17, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Nicolas STAMPF
    Replies:
    0
    Views:
    406
    Nicolas STAMPF
    May 4, 2004
  2. Joe Van Meer

    data relations and datasets inquiry

    Joe Van Meer, May 5, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    432
    Joe Van Meer
    May 5, 2004
  3. Joe Van Meer

    data relations

    Joe Van Meer, May 5, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    386
    Joe Van Meer
    May 5, 2004
  4. =?Utf-8?B?UmF2aQ==?=

    Dropdownlist - relations - asp.net

    =?Utf-8?B?UmF2aQ==?=, May 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    2,072
    avnrao
    May 17, 2004
  5. pabbu
    Replies:
    8
    Views:
    726
    Marc Boyer
    Nov 7, 2005
Loading...

Share This Page