Cross-database keys?
-
I was thinking about keeping different "applications" in separate databases (mainly because the names get mashed a lot without much effort), eg:
- CORE
- Customers
- Sites
- Contacts
- Accounting
- Externals
- RawWeatherData
- RawBillData
- RawInventories
- Equipment
- Site_Equipment
- Maintenance
- Schedules
- Real-Time-Data (growing at ~1GB/day)
I figure this would
- Let me "plug in" applications as they are done without worrying about naming conflicts
- Keep that 1GB/day table away from all the read-mostly data
- Make the code more organized
I just tried to implement site_equipment (which should have a foreign key from siteID to Core.dbo.sites.siteID), but it appears that foreign keys cannot cross databases (even if they're on the same server)
I also tried to make a view, but foreign keys can't go to views either (and I think that might be bad from a performance standpoint).Any tips, or am I just barking up the wrong tree doing this?
-
instead of trigger may help.
-
or a classic after trigger on the table containing the foreign key - it can do a quick select to check if any rows are being inserted (or updated - still use the inserted table) in the foreign table that have no corresponding primary key in the primary table in the other database. If it finds missing rows - then it issues a rollback command.
-
"... am I just barking up the wrong tree doing this?"
Ahhh ... in word ... yes.
Wouldn't having the "code more organized" remove most of your naming conflicts?
I would think the downside of having everything in one database is a lot smaller when you consider the additional maintenance and performance requirements for all those triggers.
--------------------
Colt 45 - the original point and click interface
-
>Wouldn't having the "code more organized" remove most of your naming conflicts?
>I would think the downside of having everything in one database is a lot smaller when you consider the >additional maintenance and performance requirements for all those triggers.
Yowza... thats why I was hoping I was just doing the Foreign Key constraint wrong... triggers have their place, but I don't want to be adding them willy-nilly. So I guess this also means that you can't have a Foreign Key on a Linked/Remote database? How do you enforce referential integrity with things like "Distributed Partitioned" data (BOL: Distributed Partitioned Views)?
The other thing it would let me do (which is why I thought of this in the first place) is develop modules "outside" of the "active" databases and when its done, just add an entry to something like core..activedatabases. That way I keep tables/sprocs/things like sp_FrobNitz and tbl_wtf out of the production database. I guess the right way to do this is to have separate servers for Production and Development...
Right now, I'm working with a database with over 400 (which doesn't seem like all that much to me) tables in it; the problem is that the naming convention is bass-ackwords so it scrambles them nicely (eg: the sprocs have their actions as a prefix, so all the upd_xxx are together instead of something like customers_upd, customers_add, etc). The tables are "logically" named, but their name doesn't lend itself well to grouping.
-
You can investiage using GUID (uniqueidentifier) as the PK (primary key) and FK (foreign key).
And look into "link servers".
You can't get a "real" PK/FK relationship accross databases.
However, you could hack it if you wanted.
DB1.Emp(table).EmpId ,
DB1.Emp(table).LName
DB1.Emp(table).FName
where empid is a guid.
Then in db 2, you have a some table that is a copy (of only) the PK
DB2.EmpPKsOnly.EmpId
(where this is the only column in that table)
Then everything in DB2 bases its ref integrity on that column.
Its a hack , I don't recommend it, but am offering an idea.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply