Object Relational Mapping Issues: Suggestions Needed
I'm trying to create a good design pattern for mapping data contained in relational databases to generated business objects, but I keep hitting the wall.
Consider the following tables:
TYPE: typeid, description
USER: userid, username, usertypeid->TYPE.typeid, imageid->IMAGE.imageid
IMAGE: imageid, location, imagetypeid->TYPE.typeid
I would like to collect all information about a specific user. Building a query for this is not too difficult.
SELECT u.*, ut.*, i.*, it.* FROM user u
INNER JOIN type ut ON ut.typeid = u.usertypeid
INNER JOIN image i ON i.imageid = u.imageid
INNER JOIN type it ON it.typeid = i.imagetypeid
WHERE u.userid = @userid
The problem is that the field names collide and then I am forced to list all the fields that get out of hand quickly.
Does anyone have a decent design for this kind of thing?
I thought about fetching multiple results from a single stored procedure and then using a dataset to iterate over each one, but I'm concerned that some performance issues might bite me later. For example, instead of the above query, something like:
SELECT u.*, t.* FROM user u
INNER JOIN type t ON t.typeid = u.usertypeid
WHERE u.userid = @userid;
SELECT i.*, t.* FROM image i
INNER JOIN type t ON t.typeid = i.imagetypeid
INNER JOIN user u ON u.imageid = i.imageid
WHERE u.userid = @userid;
Does this sound like a decent solution? Can anyone anticipate any problems with this approach?
source to share
Never use the SQL wildcard character *
in production code. Always list all the columns you want to get.
Then putting some of them on top doesn't seem like a lot of extra work.
Repeat your comment requesting background and reasoning:
-
Sometimes you don't need every column from all tables, and getting them can be uselessly expensive (especially for large rows and blobs). There is no SQL syntax for "all columns except the following exceptions".
-
You cannot use aliases that you retrieve with a template. After you need an alias for any of the columns, you need to expand the wildcard to explicitly specify all of the columns.
-
If the table structure changes, eg. the columns are renamed, reordered, dropped, or added, and the wildcard retrieves them all at the position specified in the tables. This might sound like a convenience, but not when your application depends on columns in the result set by a given name or position. You can get cryptic errors when your application displays columns in the wrong order (when referencing columns by position), or displays them as empty (when referencing columns by name).
However, if the SQL columns of the name queries are explicitly specified, you can use the "Fail Early" principle. This helps in debugging as it takes you directly to the SQL query that you need to edit to accommodate the schema change.
source to share