Hi,
I have a table with 3 fileds that are only filled in a few circunstances,
let say 5 out of 100. Is it ok to separate those values into another table?
And in the case it is separated, is it ok to define a different primary key
or it would be the same primary key as the primary table?
Now it looks to me to have 2 options:
First, set a different primary key to the auxiliary table and set a FK to
the primary table.
Second, set the primary key of the auxiliary table the same as the primary
key of the primary table.
I had always thought that 1 to 1 relationship between tables have no sense,
but now I don't know how to design this.
Thanks for any help
apuyinc> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another
> table?
> And in the case it is separated, is it ok to define a different primary
> key
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
What would you set as the primary key, other than the naturally obvious
choice?
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no
> sense,
When it's potentially 1 to 0, this makes sense. Why have a bunch of columns
that are NULL 95 out of 100 times? It makes joins more complex, yes, but
you can solve this once with a view.
It can also make sense if you are dealing with two or three columns 90% of
the time, and you only care about the other 80 columns 10% of the time. Why
not leave those other columns out of all the engine's work except when you
actually need them?
A|||What I write below is my opinion :)
Logically, a table should be an entity by itself. I would say its a bad
design to split a few columns because it doesn't usually get filled.
Say for example, middle name doesn't get filled 90% of the time.
So we can say that customer_id and middle_name alone can be moved to a new
table because it will save space.
But email addess, address, telephone may be moved to a seperate table along
with customer_id. In this case you have moved the customer details away from
the customer master. It can make sense.
I would say its better sometimes to denormalize the table.
Do I make sense?
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||apuyinc wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
>
If the 3 columns in the second table are optional then such a
relationship wouldn't be 1 -> 1. It would be 1 -> {0|1}. Yes, it makes
perfect sense but don't forget the foreign key. Example:
CREATE TABLE t1 (key_col INT NOT NULL PRIMARY KEY, ... other columns);
CREATE TABLE t2 (key_col INT NOT NULL PRIMARY KEY REFERENCES t1
(key_col), col1 INT NOT NULL, col2 INT NOT NULL, col3 INT NOT NULL);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> > I have a table with 3 fileds that are only filled in a few circunstances,
> What would you set as the primary key, other than the naturally obvious
> choice?
I thought about an identity...
> When it's potentially 1 to 0, this makes sense. Why have a bunch of colum
ns
> that are NULL 95 out of 100 times? It makes joins more complex, yes, but
> you can solve this once with a view.
Mmm... you are right, I will split it and use a view.
Now I have another question regarding views. If the secondary table have a
FK to another table. The view will have it? As far as I know you can't
create a FK relationship using a view, at least in SQL 2k which I am using.
> It can also make sense if you are dealing with two or three columns 90% of
> the time, and you only care about the other 80 columns 10% of the time. W
hy
> not leave those other columns out of all the engine's work except when you
> actually need them?
> A
>
>|||>> What would you set as the primary key, other than the naturally obvious
> I thought about an identity...
That's not really a key, what is your primary key in the first table?
> Now I have another question regarding views. If the secondary table have
> a
> FK to another table. The view will have it? As far as I know you can't
> create a FK relationship using a view, at least in SQL 2k which I am
> using.
Your view doesn't have the constraint; the view is just a query. You would
just write the view as:
CREATE VIEW dbo.MyView
AS
SELECT
t1.col1,
t1.col2,
col3 = COALESCE(t2.col3, '')
-- or just t.col3 if you want NULL to appear
FROM
t1
LEFT OUTER JOIN
t2
ON
t1.PrimaryKey = t2.PrimaryKey
GO|||Thanks for the help..
"Aaron Bertrand [SQL Server MVP]" wrote:
> That's not really a key, what is your primary key in the first table?
The primary key is codeId so... the primary key of the second table will be
codeId too.
> Your view doesn't have the constraint; the view is just a query. You woul
d
> just write the view as:
> CREATE VIEW dbo.MyView
> AS
> SELECT
> t1.col1,
> t1.col2,
> col3 = COALESCE(t2.col3, '')
> -- or just t.col3 if you want NULL to appear
> FROM
> t1
> LEFT OUTER JOIN
> t2
> ON
> t1.PrimaryKey = t2.PrimaryKey
> GO
>
>|||what do you mean by file sorted?
--
"apuyinc" wrote:
> Hi,
> I have a table with 3 fileds that are only filled in a few circunstances,
> let say 5 out of 100. Is it ok to separate those values into another tabl
e?
> And in the case it is separated, is it ok to define a different primary ke
y
> or it would be the same primary key as the primary table?
> Now it looks to me to have 2 options:
> First, set a different primary key to the auxiliary table and set a FK to
> the primary table.
> Second, set the primary key of the auxiliary table the same as the primary
> key of the primary table.
> I had always thought that 1 to 1 relationship between tables have no sense
,
> but now I don't know how to design this.
> Thanks for any help
> apuyinc|||I am terribly sorry. Wrong post.. again :P
--
"Omnibuzz" wrote:
> what do you mean by file sorted?
> --
>
>
> "apuyinc" wrote:
>
Jens K. Suessmeyer wrote: