Data Sources have Foreign Key ids, want the descriptiosn

[SonOfZeus]

I am working on a Bid, Bid Details data entry screen and have decisions to make on how to work with Grids/Datasets/Lookups and Templates. My table layouts are as follows:

Bid
Bid_ID, Company_ID, Bid_Date ...

Bid_Item
Bid_ID, Item_ID, Cost_Category_ID...

Item
Item_ID, Item_Name...

Item_Item_Category
Item_ID, Category_ID ...

Item_Category
Category_ID, Category_Name ...

When displaying the Bid Items, I wish to show the Name of the item and category not the ids(Foreign Keys) and am not sure if the grid should have a Select statement for it's datasource, and if it does how does this affect changes and updates, I know if the dataset is bound directly to a table it isn't an issue.

My users are currently using a system where when they tab I am working on the Bid, Bid Details data entry screen and have decisions to make on how to work with Grids/Datasets/Lookups and Templates. My table layouts are as follows:

Bid
Bid_ID, Company_ID, Bid_Date ...

Bid_Item
Bid_ID, Item_ID, Cost_Category_ID...

Item
Item_ID, Item_Name...

Item_Item_Category
Item_ID, Category_ID ...

Item_Category
Category_ID, Category_Name ...

When displaying the Bid Items, I wish to show the Name of the item, not the description and am not sure if the grid should have a Select statement for it's datasource, and if it does how does this affect changes and updates, I know if the dataset is bound directly to a table it isn't an issue.

I also have a couple quick questions on allowing users to add records, should it be text fields on the form or a second grid based on a select routine. My users are currently using a system where when they tab off the last fiend it auto adds a new record off the last field it auto adds a new record.

Has anyone encountered examples which I could gain an insight through

 

[djinn01]

First off, if you wish to show a foreign table descriptive field, you will need to use a select query that uses joins such as:

select Bid_Item.*, Bid.Bid_Date
from Bid_Item outer left join Bid
on Bid_Item.Bid_Id = Bid.Bid_Id

(you may continue with more similar outer left join's, usually one per foreign table)

I have not personnally used queries as datasources, and I don't know how the grid would react to this for updating the data. However, if you define the query as a view and your DMBS supports "view for update" (MSSQL does to some degree), then the grid/dataset/dataadapter team should work similarly with the view as it does with a straigth table.

HOWEVER...

1) Updating views always has limitations. You should play around with straight sql queries to see how your views accept updates with your specific DBMS.

2) I do not believe inserting into a view is possible at all.

3) In any event, providing a mechanism for users to enter only valid values in foreign key fields is not simple. As a rule, I never allow editing foreign-key fields in a table as that typically requires drop-down selectors and I find them messy in a table layout.

4) So, if you plan on allowing changes only to non-foreign fields and not allowing for inserts, the view in grid approach may work.

5) Generally speaking, I have found that modifying data in a grid is quite error-prone and never as self-documenting as a full Edit page can be. I understand you users have been used to a table-editing mode, but it just might be that they'd be better off using a full-page edit mode triggered by clicking on the record from the table.

6) Finally, if the table-editing mode is important, you may want to consider DE-NORMALIZING your database. Everybody knows that a fully normalized relational database is good. People however often forget that the last stage of normalization is to de-normalize for performance reasons (e.g. repeat the Type description field with each item record rather that use an ItemType table with only an ID and a Description field). By de-normalizing part of your model, you may be able to use table edits easier, in particular when it comes to adding new rows.

This may be more of a Database discussion than a C# discussion, my apologies to the C# forum members. Yet, what would C# be if it did not allow database use?