SQL tables suggestion needed

Dear Experts,
Diagram for analysis and recommendation

Thanks.
JimiJ13I T ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
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
HainKurtSr. System AnalystCommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

HainKurtSr. System AnalystCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
(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
HainKurtSr. System AnalystCommented:
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
Scott PletcherSenior DBACommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
JimiJ13I T ConsultantAuthor Commented:
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
HainKurtSr. System AnalystCommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
JimiJ13I T ConsultantAuthor Commented:
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
JimiJ13I T ConsultantAuthor Commented:
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
JimiJ13I T ConsultantAuthor Commented:
Thank you guys for your contributions. It somehow helped me made a final decision.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.