October 28, 2011 at 1:34 pm
Hello,
I have a Person table
I would this table to be usable for Persons and Companies.
I the record is a person (FK_PersonType = 1), I store First Name and Last Name in my user table, so Name in Person is nullable and read only.
If the record is a company (FK_PersonType = 2), I have to store the company name in the Name field of the Person Table, First Name and Last Name of the User table are set to the company first user (read only).
Could you help my to build the constraint ?
In advance, Thanks a lot for your help...
Best regards,
Laurent
October 28, 2011 at 2:00 pm
info-651246 (10/28/2011)
Hello,I have a Person table
I would this table to be usable for Persons and Companies.
I the record is a person (FK_PersonType = 1), I store First Name and Last Name in my user table, so Name in Person is nullable and read only.
If the record is a company (FK_PersonType = 2), I have to store the company name in the Name field of the Person Table, First Name and Last Name of the User table are set to the company first user (read only).
Could you help my to build the constraint ?
In advance, Thanks a lot for your help...
Best regards,
Laurent
You really should not store company data in a person table. The fact that you are struggling with forcing the data to fit is a clear indication this is not the best approach. You are going down a path that will cause you to commit hari kari at some point. Look how hard it is to explain what your table even contains.
Q - "Where do you store your company data?"
A - "In the Person table of course".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 28, 2011 at 5:21 pm
Hello,
Thanks for your answer. In fact, I consider Person as an abstract object. It's not a human person, human person is User.
I'm working on a e-commerce portal.
All individual person are attached to a User. Those users can be :
Client (of my app) Physical person or company
Subscriber (My app subscriber attached to client) created during subscription process
Administrator created by subscriber attached
Partner (usually a company but could be a physical person like a translator,a writer or a provider)
Buyer (Company or physical person)
Identified user on the portal like a Newsletter subscriber.
The real name of the Person table is Contact_Details.
USE [GroupPurchasePortal]
GO
/****** Object: Table [BNS].[Contact_Details] Script Date: 10/29/2011 01:10:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [BNS].[Contact_Details](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[FK_Application] [uniqueidentifier] NOT NULL,
[FK_TypeContact] [int] NOT NULL,
[FK_Contact_Address_Delivery] [int] NULL,
[FK_Contact_Address_Billing] [int] NULL,
[FK_Contact_Address_Post] [int] NULL,
[FK_Genre] [int] NULL,
[FK_Users] [uniqueidentifier] NOT NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[rowversion] [timestamp] NOT NULL,
CONSTRAINT [PK_DetailsPartenaire] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Application] FOREIGN KEY([FK_Application])
REFERENCES [SEC].[Application] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Application]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Contact_Address_Billing] FOREIGN KEY([FK_Contact_Address_Billing])
REFERENCES [BNS].[Contact_Address] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Contact_Address_Billing]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Contact_Address_Delivery] FOREIGN KEY([FK_Contact_Address_Delivery])
REFERENCES [BNS].[Contact_Address] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Contact_Address_Delivery]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Contact_Address_Post] FOREIGN KEY([FK_Contact_Address_Post])
REFERENCES [BNS].[Contact_Address] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Contact_Address_Post]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Contact_Details] FOREIGN KEY([Id])
REFERENCES [BNS].[Contact_Details] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Contact_Details]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_Genre] FOREIGN KEY([FK_Genre])
REFERENCES [BNS].[Genre] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_Genre]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_TypeContact] FOREIGN KEY([FK_TypeContact])
REFERENCES [BNS].[TypeContact] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_TypeContact]
GO
ALTER TABLE [BNS].[Contact_Details] WITH CHECK ADD CONSTRAINT [FK_Contact_Details_User] FOREIGN KEY([FK_Users])
REFERENCES [SEC].[User] ([Id])
GO
ALTER TABLE [BNS].[Contact_Details] CHECK CONSTRAINT [FK_Contact_Details_User]
GO
For technical reasons, I would have only one table to manage Physical person and companies because the purchase process is exactely the same the only difference is The name I need for the company.
I also tryed an other structure but it's to compex
I Use an ORM My business logic is in my data repository not in stored proc. I'll only create SP at en to tune some treatements...
At this time, I just need my constraint, I'll tune later.
If it's not possible in SQL Server I'll secure it in my code.
Thanks for your help,
Best regards,
Laurent
October 29, 2011 at 3:50 am
First of all, do yourself a favor. Use correct name, do not alter the names when you post. It is a mere mess to decipher your code trying to understand what names in the DDL match which name in your post.
Apart from this, I think all you need is a check constraint:
check ((FK_TypeContact = 1 and [Name] is null) or (FK_TypeContact = 2 and [Name] is not null))
October 29, 2011 at 5:18 am
Thanks a lot, I Try 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply