1:1, multiple origins, design question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

1:1, multiple origins, design question

Author

Topic

Palladino
Starting Member

3 Posts

Posted - 2005-09-05 : 11:39:00

Hello to all!

This is my first message. I hope to become, inside of my possibilities, a constant collaborator of the site.

I am with a design doubt and would like to hear some opinions. In the software that I am developing there are several tables whose origin of the relationship can be "N" other tables. For sample:

--------- TableX
TableA--------|
--------- TableY
|
--------- TableZ

I didn't want that "TableA" if it seemed with this:

TableA

TableXCode (CAN BE NULL)
TableYCode (CAN BE NULL)
TableZCode (CAN BE NULL)

I find strange, but some my friends find this viable solution. The one that I am wanting do is the following:

TableA
TableACode (PK)

-----------------
TableA_TableY
TableACode (FK)
TableYCode (FK)

TableA_TableX
TableACode (FK)
TableXCode (FK)

TableA_TableZ
TableACode (FK)
TableZCode (FK)

-----------------------
TableY
TableYCode (PK)

TableX
TableXCode (PK)

TableZ
TableZCode (PK)

Some idea or suggestion on this problem type?

Thank´s advance,

Marcelo Palladino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-05 : 11:43:06

both solutions are viable...
question is which one suits you best.
for more info provide more info, like what should your db do?

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-05 : 18:21:23

Tell us more about what You are modeling.
Intuitively I prefer your solution.

Palladino
Starting Member

3 Posts

Posted - 2005-09-06 : 08:49:33

Before anything else, thank you for the answers. Now, let to be me to be clear as for the problem:

I have a table that will store the movements, that we called in the mark of our business, "centers of costs and expenses". These movements can have "N" origins. Some examples of these origins are:

Cash flow
Adjustments of stocks
Entrance of receipts
...

I think can have up to six origins.

When the amount of data in these tables, the tendency is that the tables origins have much more registrations than the table of movement of centers of costs and expenses (in spite that with the time this table should also be very populated). This is due to the fact that nor all movement affects center of costs or expenses.

The one that I am thinking on a movement table (that historically will be very big) the optimizer is been will feel well with keys NULL. I could have a record like this (in the first solution):

Origin1 Origin2 Origin3 Origin4 Origin5
----------------------------------------
10 NULL NULL NULL NULL

Another thing that comes to mind the index is been for these columns (that can be NULL) it will be as efficient as would owe in a JOIN.

Marcelo Palladino

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-06 : 11:27:03

if there'll be a lot of nulls in there it go with your option.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-06 : 15:31:11

This is what I think:

Origin1 Origin2 Origin3 Origin4 Origin5
----------------------------------------
10 NULL NULL NULL NULL

It is not normalized, the attributes are dependent on each other.
You cannot update one attribute without having to update another.

Store the data in a normalized format as you suggested,
if you need to view the data in the format above, you can create a view that gives you that.
Something like this might do the trick;

SELECT Origin1, NULL AS Origin2, NULL AS Origin3, ... FROM TableA_TableX
UNION ALL
SELECT NULL AS Origin1, Origin2, NULL AS Origin3, ... FROM TableA_TableY
UNION ALL
SELECT NULL AS Origin1, NULL AS Origin2, Origin3, ... FROM TableA_TableZ

Palladino
Starting Member

3 Posts

Posted - 2005-09-07 : 14:31:59

OK. This discussion helped plenty, I am going for the solution more done normalize.

Hugs,

Marcelo Palladino

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources