Solved

SQL tables suggestion needed

Posted on 2014-12-23
14
52 Views
Last Modified: 2015-01-06
Dear Experts,
Diagram for analysis and recommendation

Thanks.
0
Comment
Question by:JimiJ13
  • 8
  • 5
14 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40515740
which columns are referencing in this diagram?
is ConID & ComID referring to DetID?

maybe you can create one table that joins cons & comm, say ConCom

ConCom (DetID, description, type, remarks) (FK:DetID > Order.DetID)

where type=0,1

ConDetails (detID,A,B,C,D,E,F) (FK:DetID > ConCom.DetID)
ComDetails (detID,X,Y,Z,S,T,U,W) (FK:DetID > ConCom.DetID)
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40515746
maybe i got it totally wrong :)

maybe your design is ok, and relations are other way

Order.RefNo >>>> (Cons.ConID or Comm.ComID) which you can achieve this with a trigger or maybe a check constraint instead of FK
0
 

Author Comment

by:JimiJ13
ID: 40515962
Hi HainKurt,

The relationship is Cons.ConID >>> Order.RefNo and Comm.ComID >>>  Order.RefNo. They are both unenforced which means after selecting either Cons.ConID or Comm.ComID, they can be disintegrated when deleted from parent table. I'm thinking of doing trigger to check if the those IDs are being used  before deleting / updating to protect the data integrity, but is it worth or that's really the way to go?

Using Check Constraint is one thing I have not tried yet. If you can give me a sample script that I can try, that would be great!

Thanks.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 51

Accepted Solution

by:
HainKurt earned 350 total points
ID: 40515996
maybe you can create a function which return 0,1

CREATE FUNCTION [dbo].[chk_ParentOrder](@DetID int) RETURNS int AS
BEGIN
	if exists (
		SELECT 1 FROM cons where ConID=DetID
		union
		SELECT 1 FROM coms where ComID=DetID
	) return 1 else return 0;
END

Open in new window


then in Order table (by the way, dont use Order as a table name :)

ALTER TABLE [Order]
ADD CONSTRAINT chkParentItem CHECK (dbo.chk_ParentOrder(DetID) != 0 );

Open in new window

0
 

Author Comment

by:JimiJ13
ID: 40516104
(by the way, dont use Order as a table name :)  - Thanks for the reminder (restricted name).  Anyway, this is just to illustrate my issue but not the actual database I am working on.

BTW: When I execute this script, this is an error:

CREATE FUNCTION [dbo].[chk_ParentOrder](@RefNo Varchar(21)) RETURNS int AS
BEGIN
      if exists (
            SELECT 1 FROM cons where ConID=@RefNo
            union
            SELECT 1 FROM comm where ComID=@RefNo)
                
      return 1 else return 0;
END

This is the error:
Msg 455, Level 16, State 2, Procedure chk_ParentOrder, Line 8
The last statement included within a function must be a return statement.

What do you think?
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40516452
i did not test the code, but you should be able to figure it out...

something like this:

CREATE FUNCTION [dbo].[chk_ParentOrder](@RefNo Varchar(21)) RETURNS int AS
BEGIN
  declare @result int =0;
  set @result=sign ((SELECT count(1) FROM cons where ConID=@RefNo) + (SELECT count(1) FROM comm where ComID=@RefNo));
  return @result;
END

Open in new window

0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 40516721
What type of data do "Comm" and "Cons" contain?

Never combine tables simply because they have similar data names or structures.  Combine only based on functionality or other actual data relationship(s).

It's odd to have to insert a row to a child/subordinate relationship before having to insert to the main table.  You can easily create such a function, just be aware that the order of actions in the app may then seem odd.


CREATE FUNCTION [dbo].[chk_ParentOrder](@RefNo Varchar(21))
RETURNS int
AS
BEGIN
RETURN (
       SELECT CASE
            WHEN EXISTS(SELECT 1 FROM cons where ConID=@RefNo)
                  OR EXISTS(SELECT 1 FROM comm where ComID=@RefNo)                
            THEN 1
            ELSE 0
            END
)
END --FUNCTION


Btw, if you use singular names in your organization, and you have a table that contains what your business calls "orders", then you should name the table "order".  The model should be as business-accurate as possible, technical considerations are secondary.  Simply delimit the table name as needed.
0
 

Author Comment

by:JimiJ13
ID: 40517547
ScottPletcher,

Thanks for your comments & suggestions. I got what you mean - the Test db is just for illustration purposes.
Comm is for commercial items and Cons for Consumer and the Order is the detail of the Booking transaction (not shown).

ConID & ComID are item codes related to RefNo with unenforced constraints.
My Challenge with the present schema is how to keep the RefNo from being orphan.
0
 

Author Comment

by:JimiJ13
ID: 40517550
HainKurt,

The function was successfully created.  However, adding Check Constraint on table Order will not prevent the ConID and ComID from being deleted or changed after being used in table Order?

What do you think?

Thanks.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 40517643
for that one you need delete and updatetrigger
here you will check child in both tables and if there are children you will delete children or give exception
similarly in update trigger you will also update children or give exception

here is a sample for delete

CREATE TRIGGER [dbo].[trg_DelCom] ON [dbo].[Comm] FOR DELETE AS
BEGIN
  IF Exists (select 1 from [Order] where DetID in (select ComID from deleted))
    BEGIN
        RAISERROR( 'Children exists!', 16, 2 )
        ROLLBACK
    END
END

Open in new window


you should add another one to the Cons table...
0
 

Author Comment

by:JimiJ13
ID: 40517959
HainKurt,

From the initiation of this DB design these kind of remedies are already in my mind and that's the reason I asked for other / better suggestions.

So the question still remains on the design. There is actually another thread that suggest some good points.    
http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28586183.html

Thanks.
0
 

Author Comment

by:JimiJ13
ID: 40518075
Hello Guys,

Just to complete my evaluation I need to have two triggers (Delete & Update) for each parent (Comm or Cons) table.
Is there a way to combine them is single trigger for each parent table?

These are the triggers:
CREATE TRIGGER [dbo].[trg_DelCom] ON [dbo].[Comm] FOR DELETE AS
BEGIN

      SET NOCOUNT ON;

  IF Exists (select 1 from [Order] where RefNo in (Select ComID FROM Deleted))

    BEGIN
        RAISERROR( 'Children from Order exists!', 16, 2 )
        ROLLBACK
    END
END

CREATE TRIGGER [dbo].[trg_UpdCom] ON [dbo].[Comm] FOR UPDATE AS
BEGIN

      SET NOCOUNT ON;

  IF UPDATE (ComID)
  IF Exists (select 1 from [Order] where RefNo in (Select ComID FROM Deleted))

    BEGIN
        RAISERROR( 'Children from Order exists!', 16, 2 )
        ROLLBACK
    END
END

Thanks.
0
 

Author Comment

by:JimiJ13
ID: 40520273
Hello Guys,

Can you give me some pros and cons using this design and the recommendation to use the 2 foreign key columns and a constraint instead.

Thanks.
0
 

Author Closing Comment

by:JimiJ13
ID: 40534929
Thank you guys for your contributions. It somehow helped me made a final decision.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question