Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    A little help needed with my database design?

    Hello I need a bit of advice regarding the design of part of my database. I am doing it as a hobby

    I am designing a customers-bookings databse in SQL Server Management Studio Express, and designing the front end in VS2008 (vb.net) using bindingsources and datagridviews to display the data.

    My database has 4 related tables which are displayed in their own datagridviews -

    tblCustomers
    customerID (pk)
    customerName
    customerAddress

    tblBookings
    bookingsID (pk)
    customerID
    dateofbooking

    tblBookingsItems
    bookingsItemsID (pk)
    bookingsID
    stockID
    quantityRequested

    tblStock
    StockID (pk)
    stockTitle
    Description
    NumberInStock

    When I select a customerID (via the Customer datagridview) I can view the related BookingsID's in the bookings DGV. Then I can select a BookingsID to view the related BookingsItemsID's in the bookingsItems DGV. This allows for a single booking to have more than one stockID added to it.

    What I cannot do is select a BookingsItemsID from bookingsItems DGV and view the particular StockID they have booked in the Stock DGV. I CAN do it the other way around - select a StockID to view all the BookingsItemsID for that stockID.

    I think it is becuse my DB is designed so one StockID has many BookingsItemsId's.

    What do I need to alter? Do I need to add another table called say "tblStockItems" and add the stockID and create a new field called say "stockItemsID" linked to tblBookingsItems? Or should I use a Query to show tblbookingsitems and related items on tblstock?
    Last edited by moss2076; 09-04-08 at 11:20.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by moss2076
    What I cannot do is select a BookingsItemsID from bookingsItems DGV and view the particular StockID they have booked in the Stock DGV.
    Why cannot you do this? Easy, this is.

    Code:
    select	tblStock.*
    from	tblStock
    		inner join tblBookingsItems on tblStock.StockID = tblBookingsItems.StockID
    where	tblBookingsItems.BookingsItemsID = [YOURID]
    As a matter of fact, if all you want is the StockIDs for a given BookingsItemsID, you can get that just from the BookingItems table alone.

    And do yourself a favor and drop the ridiculous "tbl" prefixes before you tie too much code to this naming convention.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2004
    Posts
    364
    Quote Originally Posted by blindman
    Why cannot you do this? Easy, this is.

    Code:
    select	tblStock.*
    from	tblStock
    		inner join tblBookingsItems on tblStock.StockID = tblBookingsItems.StockID
    where	tblBookingsItems.BookingsItemsID = [YOURID]
    As a matter of fact, if all you want is the StockIDs for a given BookingsItemsID, you can get that just from the BookingItems table alone.

    And do yourself a favor and drop the ridiculous "tbl" prefixes before you tie too much code to this naming convention.
    Drop the ridiculous "tbl"prefixes?? I have always been taught the opposite - if its a table it helps to use tbl, if its a form use frm etc etc, it really does help me to do it that way, anyway its personal choice so if you dont like it thats fair enough Im going to keep it like that..

    I dont just want the stockId's from tblBookingsItems, I know I can have that. I want to select a stockID in tblBookingsItems via the BookingsItems DGV and have tblStock's DGV display the related stock details for that selected stockID. But the way the db is at the moment is - one stockID (tblStock) to many stockID (tblBookingsItems) - hence I can only select a stockID and its view related bookingsItems. I want it the other way round - select a BookingsItemsId and view its related Stock Details.
    Last edited by moss2076; 09-04-08 at 13:14.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Experienced DBAs scoff at such naming conventions. But, if you don't mind a little scorn and snickering, forge ahead.

    Regarding your issue, my point is that there is nothing in the database schema that prevents you from doing what you want to do. If for some reason the Data Grid Views do not allow this then that is a short-coming of theirs that you will have to work around.
    Do NOT change your schema to adapt to limitations of the tools that you choose to use.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Aug 2004
    Posts
    364
    Ok as long as I dont have to add another table between tblStock and tblBookings then I must be heading in the right direction.

    I have tried the following code in vb.net to create relationships and for my tblcustomers-tlbookings, and tblbookings-tblbookingsitems the code works great, but for the following tblbookingsitems-tblstock vb.net doesnt want to know -

    Code:
    'Preparing tblBookingsItems
    
            BookingsItemsDataAdapter.Fill(ds, "tblbookingsItems")
            datatable = ds.Tables("tblbookingsitems")
    
            BookingsItemsBindingSource.DataSource = ds
            BookingsItemsBindingSource.DataMember = "tblBookingsItems"
            dgvBookingsItems.DataSource = BookingsItemsBindingSource
    
            'Preparing tblStock
            stockDataAdapter.Fill(ds, "tblstock")
            datatable = ds.Tables("tblStock")
    
            Dim relationsStockBookingsItems As New DataRelation("StockBookingsItems", _
                               ds.Tables("tblstock").Columns("stockID"), _
                               ds.Tables("tblbookingsItems").Columns("stockID"))
            ds.Relations.Add(relationsStockBookingsItems)
    
            stockBindingSource.DataSource = BookingsItemsBindingSource
            stockBindingSource.DataMember = "StockBookingsItems"
            dgvStock.DataSource = stockBindingSource
    DataMember property 'StockBookingsItems' cannot be found on the DataSource.
    - second to last line of code.

    I can get that code to work the other way round so I can select a row in tblStock and display its related bookingsitems details. But I need to select a row in tblBookingsItems and display its related Stock details.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Have you thought about doing this the PROPER way, and writing a Stored Procedure to return this data rather than linking your application directly to production tables?

    Sorry, but I'm not a VB developer, so I can't tell you how to work around its foibles. All I can tell you is that your database is designed fine.

    When other solution you might try, rather than creating a new table, is to create a view and link your datagrid to that.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2004
    Posts
    364
    I have only used Stored Procedures to Insert, Update, and Delete data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •