|
We have a couple of instances where this has come up in the past and now dealing with another and thought it'd be good to get input on what everyone's best recommendation and real-world experience has been with the following scenario:
1. Consider a situation where you have a single database entity that you want to relate to many other database entities.
2. For example, a table called "CommunicationLog", which is intended to store details (comments, user, date/time, etc.) related to communication with a Customer
3. However, in addition to storing the communication details in relation to the Customer, we also want to relate these records to other entities. For example, we want to identify if this communication is related to a specific Product, a Work Order, a Sales Order, a Quote, a Contract, another Employee, etc.
4. The entity we're referring to in #1 is intended to be very generic so we want it to be in one table rather than having a table for each of the entities in #3 (eg. SalesOrderCommunicationLog, WorkOrderCommunicationLog, etc). Why do we want this in one table?
4a. Maintainability - we see no reason to create multiple tables that are doing the same thing, when the only thing that differentiates each record is what the record relates to.
4b. By allowing a communication log record to be related to multiple entities it's more powerful - we're able to identify a customer complaint, for example, not only by customer, but also with a specific work order, product, etc.
4c. Having all these details in one table makes it easier to search and cross-reference comments that may be related to multiple specific identities
So hopefully that explains what we're trying to accomplish. My question is, what is the best way of implementing this, based not only on proper database form, but also considering real-world scenarios.
Here are the two approaches we've been looking at (but if you have another idea I'd be interested to hear):
Approach 1. Add a field for each new entity that this record relates to - this ensures good database integrity, but it also means that tables, relationships, stored procedures and other code need to be added/altered every time we decide that we want the Communication Log record to relate to another entity AND it results in a table with 10+ fields, of which many may be left null for a lot of the records.
Approach 2. Create a "CommunicationLogRelated" table with fields for CommunicationLogID, RelationType, and RelationID. RelationType would be used to identify the other entity that this CommunicationLog record is related to and RelationID is used to identify the PK of the other entity. The clear advantage to this is a "cleaner" table design and much easier to add new related entities. However, we lose referential integrity. I'm also not sure if there's a performance benefit to one approach vs. the other.
I've looked for patterns, but have not had any success in finding a pattern that mimics the above scenario. I'd really appreciate feedback on this, especially if you've implemented something like it in the past.
Cheers,
Chris
|
|
|
|
|
I don't know the best solution for your cases, but what I do is formally write down all of the potential entities (tables) and their attributes (columns). Once you have that, then start looking at normalizing the database. That usually makes it fairly obvious what needs to be in tables and what needs to be indexed.
And if that happens to coincide with someone's idea of a pattern, great. If not, so be it.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
I always go with approach 2, this leaves you with an extensible design which IMHO out weighs most other considerations. Yes you lose the ability to place FKs on the table (to your type) but I consider that a small price to pay for either adding columns or worse adding tables every time the client thinks up a new type.
I recently offered to castrate a junior dev who was insisting on approach 1 just to have the FKs so his diagram and therefore reports would be easier.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'll agree with you on this one.
|
|
|
|
|
Thanks for the input. I tend to be one of those people that will go with DB ref integrity when possible, because (in my experience) it's the best way of ensuring that true integrity exists - when there are too many devs on one project it just takes one lazy Friday afternoon for somethign to slip by. Yes, it can be caught in unit/qa/whatever, but controlling it at the DB is a great insurance policy.
On the other hand, we rarely, if ever, actually allow record deletion. And the second approach is definitely more flexible.
Thanks
|
|
|
|
|
Yeah, number two beats number one.
But what about taking it a step further and allowing for many-to-many association of notes to entities? Unless you invoke YAGNI, I could see the conceptual benefit of allowing multiple notes for an entity and associating one note with multiple entities. If you like, you could have a notes table for each table for which you want to associate notes; that would reduce the amount of table duplication. I wouldn't be concerned about the lack of referential integrity*; you could implement that programatically, or even have a cleanup routine to delete orphaned notes.
Another alternative is to have a notes field in each table that should support notes. That allows referential integrity, but only one note per entity -- unless you chain notes together.
* I've said it before and I'll say it again... I once worked at a place where referential integrity wasn't allowed in production databases -- once the applications were tested, it wasn't needed and it "bogged down the database"**. 
** Rdb on OpenVMS.
|
|
|
|
|
Hi - thanks for the input.
Truthfully our design already allows for a many-to-many - I just didn't include that detail here because I was trying to keep the explanation less "muddy". Our intent is to allow one or many notes to relate to one or many (and any combination of) customers, quotes, work orders, products, etc. The potential power of knowledge-base access based on this approach is great - the real question is going to be creating a UI that encourages the users to easily select the related criteria and/or BL that automatically selects the appropriate matching related items.
For ref integrity, one thing I forgot to mention is that we rarely ever allow record deletion through most of our objects so it should be even easier to control that programmatically.
I'd prefer to steer away from the one Notes field per table - that limits us to a very 80's approach and hardly moves us forward.
I admittedly do not invoke YAGNI enough, but in this case our immediate need for multiple fields already justifies the above approach; knowing that this will likely be expanded just adds yet another argument to the approach.
As for the approach of ref integrity not being allowed in production dbs... well I've personally seen the mess that can create. While that mess may certainly be related to holes in another processes (dev, testing, qa, etc.) I still don't expect to ever implement that type of policy. It's one thing to have orphaned records in a communication log, quite another to have an orphaned inventory audit trail!
Thanks so much for the reply.
Cheers,
Chris
|
|
|
|
|
G-Tek wrote: we rarely ever allow record deletion
Very wise.
G-Tek wrote: It's one thing to have orphaned records in a communication log, quite another to have an orphaned inventory audit trail!
Their point was that that would be detected by referential integrity in test. It's another reason not to use stored procedures. 
They also wouldn't allow me to create functions -- "Too much metadata! You're bogging down the database!" So I created them programatically when I needed them and dropped them when I was done.  Then they they laid me off or some reason. ::shrug::
|
|
|
|
|
Not allowing record deletions does save a lot of headaches. The reality is that in most cases it really isn't necessary. The biggest issue that we have is typically during training where dummy records get created that they don't want to see - we can, of course, delete those behind the scenes, but it still raises the question of why they don't see a delete button.
So then for some entities we have created a deleted status/flag/bit. That way the user "believes" the record is gone, but we can still magically retrieve it if necessary - or give access to high level users to access the "deleted" records.
Of course, for things like communication logs... they're logs! They shouldn't be deleted, only archived.
PIEBALDconsult wrote: They also wouldn't allow me to create functions -- "Too much metadata! You're bogging down the database!" So I created them programatically when I needed them and dropped them when I was done. Then they they laid me off or some reason. ::shrug::
I have changed my approaches over the years and have worked with people with very different approaches - from those that wanted to push all the BL to the stored procedures, to those that want to wipe out stored procedures and put everything in code. It's too bad that there is rarely a 100% right and pure answer to the scenarios we face!
|
|
|
|
|
G-Tek wrote: deleted status/flag/bit
Right. If you give them delete, they'll want undelete. 
|
|
|
|
|
hi.. everybody..
i'm using ssrs2008 without IIS ...
i've created my reports and deployed in my system.. i.e
http://localhost:8080/rpt which are working fine.. (" i.e in XP prof SP3")
now i've developed an application in different system which windows server2008
in that i'm using reportviewer now.. what i want is when i run the project in
windows server the reports display from my system.. i.e my url http://myip:8080/rpt
error msg("enable to connect remotely")
please help me...
i've tried by giving my http://myip:8080/rpt in another system .. but
it will ask username and password i've tried giving my system and password .. and also the system name and passward where i'm trying to execute.. the error is displayed
("An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.")
wat to do...
modified on Saturday, December 4, 2010 8:25 AM
|
|
|
|
|
You need to design your report as a LOCAL report rather than a published report. That means your app gets the data and delivers it to the report, the report has a RDLC extension and has no awareness of the data connection.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi.. everybody
i got the solution here..
http://community.discountasp.net/showthread.php?t=4402
but now again i'm in a problem
how to assign security role for different system in a network
for a folder or it can be report..
i want to assign diferent permission for different users..
|
|
|
|
|
Dear All,
Forgive me for asking what must seem to most like an obvious question, but I am a SQL server newbie.
I am using SQL Server 2005 and I have a certain field which contains both a city and a state in one file; i.e., Portland, Oregon will be listed as
CityAndState
------------
PORTLAND OR
Notice that this is a single VARCHAR column and there are a whole bunch of extra spaces in between the 'PORTLAND' and 'OR'.
How do I get it so I have
CityAndState
------------
PORTLAND OR
<pre>
<div class="signature">Sincerely Yours,
Brian Hart
</div>
|
|
|
|
|
you could do a search for double space, then replace double space by single space, and repeat until nothing gets found.
a possible improvement would look for 8 spaces and replace them by 1; then 4 by 1; then 2 by 1.
However, it is simply wrong to store two pieces of data in a single field, you really should split them permanently and hence avoid all future formatting issues.
|
|
|
|
|
I could say 'That is horrible', but I won't. Ooops, just did it.
|
|
|
|
|
Use the REPLACE function. Check here.
|
|
|
|
|
I would be intrigued to know why this answer (above) has been down-voted so much. I think it links to a good solution.
|
|
|
|
|
Turn it into two columns and then you can format it however you want
DECLARE @city varchar(20)
Set @city= 'Portland OR'
select
SUBSTRING(@city, 1,CHARINDEX(' ', @city)) as [city],
REVERSE((SUBSTRING(REVERSE(@city),1,CHARINDEX(' ', REVERSE(@city))))) as [state]
This assumes no trailing spaces after OR. If there are add some TRIM() in there.
|
|
|
|
|
SELECT REPLACE(CityAndState,' ','');
Code just replaces DualSpace with NoSpace.
Regards,
Hiren.
"The more we give of anything, the more we shall get back." - Grace Speare
(you can consider this quote while giving vote also)
Microsoft Dynamics CRM
|
|
|
|
|
This is no good if there are an even number of spaces between words.
|
|
|
|
|
i am unable to start the SQL Full Text Search Service from SQL Server Configuration Manager previously i successfully started the service and extracted the data from my table but after restarting the system there is an issue sarting this service
following windows msg appears:
"the request failed or the service did not respond in timely fashon. Consult the event log or other applicable error logs for details"
|
|
|
|
|
What does it say in the event log?
|
|
|
|
|
I created a set of tables in which the primary keys are set to uniqueidentifiers with a default value of newid(), which is the function that generate new guids in Transact-SQL. While I can create a uniqueidentifier variable and initialize it before inserting a row, that would obviate the reason for creating a default value in the first place.
But unlike an identity field, there is no function, that I can think of that would retrieve the most recently created uniqueidentifier. I would like to use this value within the body of a stored procedure but I can't think of any way to get it short of creating it ahead of time and not using the default value mechanism.
I could access the new value in a trigger, but then how would I pass that back to the stored procedure? I don't think that a trigger is the way to do this, but I don't know.
Can anyone show me a way to do this?
|
|
|
|
|
I hate GIUDs
I do not use the default value, instead I create the GUID (in the stored proc) before inserting and therefore already have it for the return select or an out parameter.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
