October 31, 2007 at 5:47 am
I have one table containing cellphone contracts, one table with objects (like customers, locations ...) and one containing the users:
CREATE TABLE [dbo].[tblVertraegeUndKarten](
[Rufnummer] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[ID_Benutzer] [int] NULL,
[ID_Objekt] [int] NULL,
CONSTRAINT [PK_tblVertraegeUndKarten] PRIMARY KEY CLUSTERED
(
[Rufnummer] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tblBenutzer](
[ID_Benutzer] [int] IDENTITY(1,1) NOT NULL,
[Vorname] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Nachname] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL
CONSTRAINT [PK_tblBenutzer] PRIMARY KEY CLUSTERED
(
[ID_Benutzer] ASC
) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[tblObjekte](
[ID_Objekt] [int] IDENTITY(1,1) NOT NULL,
[BezeichnungObjekt] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_tblObjekte] PRIMARY KEY CLUSTERED
(
[ID_Objekt] ASC
) ON [PRIMARY]
) ON [PRIMARY]
--testdata
INSERT INTO [dbo].[tblObjekte]
([BezeichnungObjekt])
VALUES
('Walmart')
INSERT INTO [dbo].[tblBenutzer]
([Vorname]
,[Nachname])
VALUES
('John'
,'Doe')
INSERT INTO [dbo].[tblVertraegeUndKarten]
([Rufnummer]
,[ID_Benutzer]
,[ID_Objekt])
VALUES
('+491716452907',
1,
NULL)
INSERT INTO [dbo].[tblVertraegeUndKarten]
([Rufnummer]
,[ID_Benutzer]
,[ID_Objekt])
VALUES
('+491716452908',
NULL,
1)
Some cellphones are assigned to certain users and some to objects, so that some of the FK-columns do not contain values.
I asked myself, which would be the best practice for this scenario? If the column contains 0, there is no corrsponding value in the respective table, NULL would mean "unknown". What should I do??
November 1, 2007 at 12:14 pm
Can you put a default value for 'UNKNOWN' in both tables ? Even foreign key can be null but it lost its purpose if the value is null. It is just my opinion to put a value for 'UNKNOWN' so that we know what is going on in the tables.
my 2 cents
November 1, 2007 at 2:00 pm
Just my 2ct.
Split the table !
My guess is the must be more info appart from the relation that you can document in your relation table. e.g. startdate/enddate of the relation , who registered it, ...
This way you can avoid the NULLs, avoid ORs, have smaler sets of data, ...
You may want to search info regarding supertypes and subtypes... data analysis.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 16, 2007 at 4:27 am
Thanks for ur advices. I think I'll go for the "UNKNOWN" - value thing.
November 16, 2007 at 4:41 am
While having NULLS in the FK relation isn't unusual in itself, the fact that you have an "object" table seems to hold different kinds of entities suggests on the face of it a possible design issue. I agree with the previous post that a split might be the better solution. The use of UNKNOWN is a common bucket with a data warehouse but not so common with OLTP.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply