Design Question: Correct way to show data from mutliple DB tables
-
Hey Guys, Here's the problem: I'm trying to learn multi-tier design so I've been writing a little app to teach myself. I have a normalised SQLServer database which contains my music collection. The DB schema is here[^]. So, I used LLBLGen[^] to generate a C# class for each of my table entities and that works great. There are SelectOne() and SelectAll() methods on each of my entities which allow you to select one record or the whole table. My question is: how do I join the tables together? And how do I search them? I tried asking on the asp.net forums but they just suggested buying a OR mapper tool. Cheers James
-
Hey Guys, Here's the problem: I'm trying to learn multi-tier design so I've been writing a little app to teach myself. I have a normalised SQLServer database which contains my music collection. The DB schema is here[^]. So, I used LLBLGen[^] to generate a C# class for each of my table entities and that works great. There are SelectOne() and SelectAll() methods on each of my entities which allow you to select one record or the whole table. My question is: how do I join the tables together? And how do I search them? I tried asking on the asp.net forums but they just suggested buying a OR mapper tool. Cheers James
It will be kind of difficult using the schema you have defined. Where are your foreign keys (FKs)? The full join should look something like this (assuming proper FK development):
SELECT * FROM Artists INNER JOIN Albums ON (Artists.ArtistId = Albums.ArtistId) INNER JOIN Tracks ON (Albums.AlbumId = Tracks.AlbumId) INNER JOIN Files ON (Tracks.TrackId = Files.TrackId)The type of joins you need to develop will depend on how you want the data displayed.
-
It will be kind of difficult using the schema you have defined. Where are your foreign keys (FKs)? The full join should look something like this (assuming proper FK development):
SELECT * FROM Artists INNER JOIN Albums ON (Artists.ArtistId = Albums.ArtistId) INNER JOIN Tracks ON (Albums.AlbumId = Tracks.AlbumId) INNER JOIN Files ON (Tracks.TrackId = Files.TrackId)The type of joins you need to develop will depend on how you want the data displayed.
Thanks for the reply Michael. I know how to do the JOIN in sql, that's not the problem. The problem is that from a design point of view each table is a separate C# class upon which I call the CRUD methods (album.Delete(), album.SelectOne() etc). What I need to know is what is the best way of joining the tables under this scenario? I've been think of defining views for each JOIN which I require and then generating classes for each of those but I'm not convinced it is the best way to go about it. Cheers James
