Solved

SQL tables suggestion needed

Posted on 2014-12-23
14
49 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 51

Accepted Solution

by:
HainKurt earned 350 total points
Comment Utility
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
Comment Utility
(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
Comment Utility
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:ScottPletcher
ScottPletcher earned 150 total points
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:JimiJ13
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you guys for your contributions. It somehow helped me made a final decision.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
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…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now