Solved

SQL tables suggestion needed

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

Thanks.
0
Comment
Question by:JimiJ13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 56

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 56

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 56

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 56

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 56

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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