A little help needed with my database design?

moss2076 · #1 (**permalink**) 09-04-08, 09:17

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?

blindman · #2 (**permalink**) 09-04-08, 11:31

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.

moss2076 · #3 (**permalink**) 09-04-08, 12:08

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.

blindman · #4 (**permalink**) 09-04-08, 12:25

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.

moss2076 · #5 (**permalink**) 09-04-08, 13:03

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

Quote:

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.

blindman · #6 (**permalink**) 09-04-08, 14:56

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.

moss2076 · #7 (**permalink**) 09-04-08, 15:56

I have only used Stored Procedures to Insert, Update, and Delete data.

Internet Services

Web Development

Digital Marketing

Consumer Tech