Table

Last Post 09 Feb 2011 07:38 PM by russellb. 8 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
river1
New Member
New Member

--
08 Feb 2011 03:55 AM
Hi,

   I'm seeing a table in a database of a client which have a column that is foreigh key of two differente columns of two differente tables.

   I didn't knwed that this was possible....


Is this correct? having one column as foreigh key of two differente columns in differente tables?


Thank you
rm
New Member
New Member

--
08 Feb 2011 05:52 AM
Depends on app logical.
river1
New Member
New Member

--
08 Feb 2011 07:27 AM
Ok.

What about this case:

suppose you need to create one obejct that returns this infromation :


Name, Value

Pedro, 1
Martha, 2

If you cannot link (foreigh key) this object with other tables already in database, what would you do? would you still create a table or would you create a view?

I ask this because some of the people that work ear with me, create tables that have only two or tree lines instaed of creating a view.


Would it be more correct if i create a view like:

select 'pedro' Nome, '1' value
union
select 'pedro2' Nome, '2' value


Intead of creating a table?


I think that we must not create tables on databases if we can not link it to one of the other tables already in the database.

What do think about this?


Thank you
rm
New Member
New Member

--
08 Feb 2011 01:15 PM
Are those info in separate tables? What are table schema?
river1
New Member
New Member

--
09 Feb 2011 12:34 AM
This kind of tables, like table Coins which has tree rows:

Description, value

Dollar 1
Kwanza 2
chf 3

They are in the same schema as the other tables, but they can not have a rellation with other tables because they don't have any columns that can be foreigh keys / primary keys of other tables.


Other example of this kind of tables it's this:

cod, name
1 SGRP
2 SGT
3 SGTA
4 SGCC
5 SGIPU
6 SGDA
7 SGQ
8 SGSC


The question is:

Do you create tables in your database that can not reference or be referenced by other tables? if yes, when? only when they are in differente schemas?


Thank you.
rm
New Member
New Member

--
09 Feb 2011 06:30 AM
How do those 2 tables relate each other? Didn't see common column in them. It's nothing to do with schema since you can join tables cross schemas as long as have proper permission.
river1
New Member
New Member

--
09 Feb 2011 06:33 AM
they don't relate, that's why i think that it is better to create views. I don't think that it is a great idea to have tables ina database that are not related with other tables, correct?
rm
New Member
New Member

--
09 Feb 2011 06:36 AM
Why put numbers together if they are not related at all?
russellb
New Member
New Member

--
09 Feb 2011 07:38 PM
I'm not following you exactly Pedro, but to the initial question -- of course it's possible and quite common.

Suppose I have these 3 tables
Create Table Customers (CustomerID int, name varchar(32));
Create Table CustomerLocations (locationID int, CustomerID int, Address varchar(40), city varchar(30));
Create Table CustomerNotes (CustomerNoteID int, CustomerID int, note varchar(255));

I'll FK CustomerLocations.CustomerID to Customers.CustomerID
And I'll FK CustomerNotes.CustomerID to Customers.CustomerID

Regarding your view definition, which I don't see it having anything to do with the original question...If you want ALL of the values in the 2 select statements, use UNION ALL instead of UNION. This prevents the optimizer from having to do a distinct search on the result.


Acceptable Use Policy
---