Data Binding with Relationships

[Guest]

Hi everyone,

I am writing some C# code using Visual Studio 2003 Enterprise Architech to
allow addresses in a database, to be accessesd so that the data can be
validated. I have 4 basic tables on the SQL Server 2000 (with SP3a).

These tables are named Addresses, StreetsInTowns, Streets and Towns. The
Streets table has a Primary Key named StreetID and the Towns table has a
primary key TownID. Both tables has a column named Name for the Street Name
or Town Name, we have some other info but for this we can ignore.

The Addresses Table contains the things like the Building Number and
Building Name and has 2 columns named StreetID and TownID that are related to
The StreetID column of the Streets table and the TownID column of the Towns
table. In our last table StreetsInTowns we have a Primary key StreetTownID
and 2 columns named StreetID and TownID, StreetID is related to StreetID of
the Streets table and the TownID of the Towns table. This table is used to
tell us what Towns has what Streets or what Streets are in what Town, example
- we could have a High Street in Anytown and Bigtown. Now what I am trying to
do is have 2 comboBoxes, 1 for Street and 1 for Town on a Windows Form, and
on a Web Form for the Web System, and at runtime the Street comboBox loads
all the Streets and the Town comboBox loads all the Towns. When say the
Street is changed I want the Town comboBox to load all the Towns that the
Street is associated with. And if the Town is changed I want all the Streets
in that town to load. I am looking for a simple way of doing, I have tried
working with views but it's too complicated for my head. I know their is a
simple way to achive what I want but don't really know how, I have read about
reversing the DataRelations Keys but this sounds like it could have problems
long term. I am working with a DataSet where the tables are all related. I
would like to link all this without too much coding.

Cheers in advance guys. If I have not been clear let me know!!!!

 

[Nicholas Paldino [.NET/C# MVP]]

Paul,

Personally, I think the data model is incorrect. Even though two
streets in two towns might have the same name, they are different entities,
and (I believe) should be treated as such.

You might be saving some DB space, but you are increasing the complexity
WAY too much using this design. You don't get much in the way of ease of
use with this model.

Basically, I would have the street have a FK to the town it is a part
of, and if there are two streets with the same name, then they have two
entries in the database.

If you do it this way, then when showing the streets for a particular
down, you just have to create the relation between the street and town, and
call the GetChildRows method on the record that you are looking at in the
town table.

Better yet, if you bind the streets list to the relation, and not the
child table, then the binding mechanism will do it for you, automatically,
depending on the town selected.

Hope this helps.

 

[Guest]

Nicholas,

Thanks for your thoughts on my data modal. I am redesigning it as some where
in your thoughts I picked up on a possible problem in the future with the
data modal, that being what would happen if a Street was renamed!! So yeah my
data modal is incorrect so thanks for your input.


Dr. Paul Caesar
CoullByte (UK) Limited