Is this bad design?

Hey,

If you have a one to many relationship, say a product has many reviews, is it good design to have a flag in the product which indicates whether there are any review in the reviews table?

One might do this so that in a stored procedure one can check to see if there are any product reviews before running a select query on the reviews table. If there aren't then you've saved a select query.

Also, this flag could be used to determine the type of formating required for the data. I suppose it would be quite easy to just look up the count of reviews in the data table to gather the same info.

Cheers,

I.I hate designs like that. That sp is still going to have to query the product table to check the flag, so you haven't gained a thing. A simple Count Where fk=pk will tell you just as fast, or just do the query--you're going to want the records anyway.

The only way I could see this helping would be if ALL of the following conditions were met:
** This is an extremely active database that is being pushed very hard
** It is rare for there to be any reviews
** You already have the product table row

But what a headache to try to keep that flag updated. Maybe a trigger could be used -- add a record, increment the count, delete a record, decrement the count.|||It didn't feel right either.

I think another problem with it is that if a sp determines that there are reviews to gather and then a concurrent user goes and deletes all the reviews, the first sp will return no reviews so the formatting will be set up to show reviews but they'll be none.

"That sp is still going to have to query the product table to check the flag, so you haven't gained a thing."

If you had several flags for several tables then it might save a few selects. You could get all the flags in a single select.

Is this an efficient way to return a comma string

Hi there,
I have created a sp and function that returns amongst other things a
comma seperated string of values via a one to many relationship, the
code works perfectly but i am not sure how to test its performance.. Is
this an efficient way to achieve my solution.. If not any suggestions
how i can improve it.. What are the best ways to check query speed?
MY SP:
CREATE PROCEDURE sp_Jobs_GetJobs
AS
BEGIN
SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
AS location, salary, summary, logo
FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
ORDER BY j.Inserted DESC
END
GO
---
MY Function:
CREATE FUNCTION fn_GetJobLocations (@.JobID int)
RETURNS varchar(5000) AS
BEGIN
DECLARE @.LocList varchar(5000)
SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
FROM Jobs_Locations l inner join List_Locations ll on
ll.LocationID = l.LocationID
WHERE l.JobID = @.JobID
RETURN @.LocList
END
Any help or guidance much appreciated...First of all, what you have in your UDF is a unsupported construct. It
exploits certain physical behaviours that might seem to work in some cases,
but can fail in a variety of situations. Being undocumented, it can change
between versions, service packs or patches.
Doing this in SQL Server invariably requires some level of looping, either
using a cursor, WHILE loop, recursion etc. In SQL 2005, there are some work
arounds using FOR XML method which in some cases can be complex and error
prone.
A good approach is to retrieve the resultset to the client side and generate
the string you need to create.
Also, just noted that you use sp_ prefix to your procedure which is not at
all recommended, since they are reserved for system procedures and can
affect performance adversely.
Anith|||3rd time tonight i've posted this solution, interesting :).
Anyway, something like this (SQL Server 2005) will do the trick and will
perform blisteringly...
select j.Id, j.Inserted, Title, Reference,
(
select location_name + ',' as [text()]
from Jobs_Locations soi
where soi.Job_ID = t.Job_ID
order by location_name
for xml path( '' ), type
)
from Jobs_Jobs as j
It will give one line per job and concatenating each location seperating
them by commas.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
<anthonykallay@.hotmail.com> wrote in message
news:1133804859.768819.33930@.g14g2000cwa.googlegroups.com...
> Hi there,
>
> I have created a sp and function that returns amongst other things a
> comma seperated string of values via a one to many relationship, the
> code works perfectly but i am not sure how to test its performance.. Is
> this an efficient way to achieve my solution.. If not any suggestions
> how i can improve it.. What are the best ways to check query speed?
>
> MY SP:
> CREATE PROCEDURE sp_Jobs_GetJobs
> AS
> BEGIN
> SELECT j.Id, j.Inserted, Title, Reference, dbo.fn_GetJobLocations(j.id)
> AS location, salary, summary, logo
> FROM Jobs_Jobs j INNER JOIN Client c ON j.ClientID = c.id
> ORDER BY j.Inserted DESC
>
> END
> GO
> ---
> MY Function:
> CREATE FUNCTION fn_GetJobLocations (@.JobID int)
>
> RETURNS varchar(5000) AS
> BEGIN
> DECLARE @.LocList varchar(5000)
> SELECT @.LocList = COALESCE(@.LocList + ', ','') + ll.location_name
> FROM Jobs_Locations l inner join List_Locations ll on
> ll.LocationID = l.LocationID
> WHERE l.JobID = @.JobID
> RETURN @.LocList
>
> END
>
> Any help or guidance much appreciated...
>

Is there such a thing as too many relationship?

Hi,

I have a corporate database with about 60 different tables that spans
manufacturing, accounting, marketing, etc.

It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields like customer_id
or product_id.

But should I do that?

My question is: Is there such a thing as too many relationships? Can
I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected and simply use
JOINS in my business code?

Thanks,
HC>> I have a corporate database with about 60 different tables that
spans manufacturing, accounting, marketing, etc. <<

That is not that big for a corporate RDBMS ..

>> It is possible, but unwieldy, to establish a relationship for each
table in the entire database through critical fields [sic] customer_id
or product_id. But should I do that? <<

What do you mean by "establish a relationship"? Build a relationship
table among all the entities in the model?

>> My question is: Is there such a thing as too many relationships?
Can I establish referential integrity via relationships with critical
tables like Accounting, but leave the rest unconnected [sic] and
simply use JOINS in my business code? <<

Conntected? You mean like in a network database with pointer chains?
You even talk about fields, not columns. Things in SQL are
referenced.

Yes, you need to get all of the business rules in your model. The
more you can enforce them with DRI actions and CHECK() constraints,
the better for you and the easier for the programmers that follow.
Otherwise, you data model is incomplete.|||jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0408131746.3edd63bc@.posting.google.com>...
> >> I have a corporate database with about 60 different tables that
> spans manufacturing, accounting, marketing, etc. <<
> That is not that big for a corporate RDBMS ..
> >> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields [sic] customer_id
> or product_id. But should I do that? <<
> What do you mean by "establish a relationship"? Build a relationship
> table among all the entities in the model?
> >> My question is: Is there such a thing as too many relationships?
> Can I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected [sic] and
> simply use JOINS in my business code? <<
> Conntected? You mean like in a network database with pointer chains?
> You even talk about fields, not columns. Things in SQL are
> referenced.
> Yes, you need to get all of the business rules in your model. The
> more you can enforce them with DRI actions and CHECK() constraints,
> the better for you and the easier for the programmers that follow.
> Otherwise, you data model is incomplete.

CELKO,

What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database?

Thanks.|||>> What I mean is that should every table in the entire database
necessarily have a relationship established to other tables via a
primary key to foreign key constraint. Do you ever have unreferenced
tables in a database? <<

Auxiliary tables, such as the calendar, would not be referenced by
another table.

Working tables used to scrub data before it goes into the schema would
not be referenced by another table. They might have few if any
constraints, so the raw data could be inspected.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||It is possible to have 'too many' relationships... though I doubt that
will be an issue for you. In other words, as everyone knows DRI exists
to help ensure data integrity. It also servers the purpose, as
intimated by Celko, of showing relations between entities...

However, each CHECK/FK creates a bit of overhead. That overhead can
end up being noticeable on VERY large tables (in the GBs and tens of
millions of rows). So there's a fine balance between optimizing
performance in some OLTP environments and keeping data clean/intact.
Good indexing can go a long way to keep all of this in check.

Case in point on the TOO MANY relationships (given all the stuff I
blabbed about above concerning costs) some over-zealous architects
will do something dumb like have an order items table. In that table
you'd normally have a FK for orderID... and for the itemID (for each
item)... but you probably wouldn't need a customerID in that table,
etc... )

Moral of the story. More is usually better. Just don't over do it.

--Mike

harris_cohen@.yahoo.com (H Cohen) wrote in message news:<1545331c.0408131453.477b3872@.posting.google.com>...
> Hi,
> I have a corporate database with about 60 different tables that spans
> manufacturing, accounting, marketing, etc.
> It is possible, but unwieldy, to establish a relationship for each
> table in the entire database through critical fields like customer_id
> or product_id.
> But should I do that?
> My question is: Is there such a thing as too many relationships? Can
> I establish referential integrity via relationships with critical
> tables like Accounting, but leave the rest unconnected and simply use
> JOINS in my business code?
> Thanks,
> HC