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 |
|
querymatic
Starting Member
1 Post |
 Posted - 2010-03-30 : 04:12:03
|
| Hi there,Tha basic question here is, can two tables share an identity sequence. So if a record is inserted in table A it would have a PK of 1, then if a record is inserted in table B it would have a PK of 2, and so on. I want two tables to be able to share a joining table. (The joining table is not between these two tables, but between each one and a third table).By the way, this setup is for use with ASP.Net Entity Framework and Dynamic Data.Scenario:Two tables of similar format, Items1 and Items2Items1------ItemID int PKName nvarchar(50)Items2------ItemID int PKName nvarchar(50)Then a third table, AttributesAttributes----------AttID int PKName nvarchar(50)Then a fourth joining table, ItemsAttributesItemsAttributes---------------ItemID int PKAttID int PKAn attribute can be common between Items1 and Items2. Then I'd like to be able to select the attributes for a row in Items1 or Items2.If the two tables, Items1 and Items2 independently have identity(1,1) set on their PK, it would mean there's no way to differentiate between rows in ItemsAttibutes.To do this, in the past I have either:(a) Added another column into ItemsAttributes called ItemTypeID so that I can differentiate in the query between Item1 and Item2.or(b) Had a uniqueidentifer column in each of the tables, Items1 and Items2. This ensured I could query, knowing the rows returned from ItemsAttributes were specific to a row in one of the items tables.The application which now connects to this DB is ASP.Net Entity Framework and Dynamic Data.In this setup joining tables with any more columns than the two PK columns causes an issue, ruling out (a). Also relationships with uniqueidentifiers don't appear in the automated EF designer. Further, I thought that using uniqueidentifiers in relationships could cause fragmentented database files eventually.So, I thought I could solve all of this by having the two tables Items1 and Items2 share a common source of identity values. We wouldn't be able to have ref integrity, but the we could manage this in the app.Can anyone advise please?Cheers |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-30 : 08:04:09
|
identity(1,2) and identity(2,2)?I would prefer to have only one table though...
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
|
|
|
|
|
Share identity sequence between tables