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.
| Author |
Topic |
|
gemmaj
Starting Member
3 Posts |
 Posted - 2008-07-03 : 10:49:46
|
| Hi,I have some problems at hand. I'm hoping any good db techie would help.First:I have 2 tables and both have some data but a one to one relationship. I am using a PK in table A. In the second table this PK would be there as a foriegn key but my colleauge said don't define another column as a primary key rather have this foreign key put as a primary key in table B.I was under the impression that a primary key should stick to one table and if you're using the same column in another table you should have it as a foreign key but my colleague is arguing that the relationship is one to one so just have it as primary key over in 2nd table. I need a detailed discussion why we want to do it or why we don't ?2nd:we have one table and there is lot of stuff which aren't related to primary key. The table will hold many thousand records.Obviously from normalization point of view you should refactor it in more tables but I'm being advised by a fellow that if you put those columns as varchar/nvarchar that wouldn't make much difference and will save the long queries of relationships and relationship cost. I'm a bit agreeing on this issue because we can't make every columns not related as tables but I'm also disagreeing as they can still be combine into one seperate extension sort of table to attach with our main. Here is the table.tbl_myDatamydata_ID intmydatafield1 nvarcharmydatafield2 nvarchar mydatafield3 nvarchar mydata_date datetime custom1 nvarchar(Max)custom2 nvarchar(Max)custom3 nvarchar(Max)custom4 nvarchar(Max)custom5 nvarchar(Max)and the list goes on. My only problem is that apart from mydataID, mydatafield1, mydatafield2 none of other ones really fill up all the time. They are for most part left blank. My colleague said its better to put them in one table but my thinking suggest to put rest of the columns in an ext table with mydata_ID as foreign key. As suggested I need detailed reply with some article or discussion of why it is wrong or right?Thanks for your help Gemma |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-07 : 01:53:24
|
| For your first, If you have the field in the second table as a foriegn key, this will enforce referential integrity to ensure that any data entered in table 2 also exists in table 1 whereas if you use a primary key, the tables can get out of step very quickly.The second problem I do not fully understand. Can you give a sample of what you expect the data to look like in your scenario as from here, this just looks like a normal table and as it stands, I can't see any issues with this design.If you need to find articles on this type of issue, use google. |
 |
|
|
gemmaj
Starting Member
3 Posts |
Posted - 2008-07-07 : 04:47:18
|
| Hi Rick,Thanks for your reply. I tried googling it but couldn't find anything. You're right but can you give us an example to persue my colleague?2nd table: I wrote "the list goes on. My only problem is that apart from mydataID, mydatafield1, mydatafield2 none of other ones really fill up all the time. They are for most part left blank"The only problem is with normalization those colums should be part of another table. If normalization up to 3NF is applied then there would be many tables. My colleague is advising that if you put these columns as varchar/nvarchar that wouldn't make much difference and will save the long queries of relationships and relationship cost. What do you suggest? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-07-07 : 05:13:59
|
| Ok, on the first point, the best I can come up with is an article for enforcing referential integrity in a database.http://www.sqlservercentral.com/articles/Advanced/enforcingreferentialintegrityinmicrosoftsqlserver2/1670/I also found this article Foreign Key vs. Trigger Referential Integrity in SQL Serverhttp://www.mssqltips.com/tip.asp?tip=1508Or one simply on Foreign Keyshttp://www.mssqltips.com/tip.asp?tip=1296This took me 5 seconds to find on google.2nd:What would be held in the columns? Are they logically different entities? Can they all be held in one field instead of lots of different ones with a pointer to which field they point to, so instead of looking for mydatafield1,2,3 and custom1,2,3 etc, you have one table with mydata_ID, FieldOrder, mydatafield, custom. This way you could easily find out how many and which of the fields are used?Without looking at the whole design though, all I can do is speculate. |
|
|
|
gemmaj
Starting Member
3 Posts |
Posted - 2008-07-07 : 11:18:17
|
| Hi Rick,Thanks for that. My colleague Bob thinks that instead of RI, a PK should be used if it is one to one relationship so is a MVP on sql server forums. So according to them we should have 2 primary keys in a one to one relationship tables.Can you declare a foreign key column as a primary key as well. What sort of problems we would get? remembering that the relationship is 1:1. 2nd: There are different columns, some storing nvarchar(max), some storing int and some storing dates. They're seperate logical entities but I'm intrigued what you're describing. Can you elaborate more with an example?Thank you very much for replies. Gemma |
|
|
|
|
|
|
|
|
table design issues.