May 14, 2009 at 9:38 am
Right now I work completely in development and not in production. Our current DB structure is around 200 tables. It has taken a while to develop around these tables and the powers that be would like some of the components of design shipped out. So now I am tasked with going from a normalized, fked 1 DB to three. They are call the "Suite".
I have many files (mostly data), but the primary files are the following
There maybe syntax issues in the following. this is only for discussion purposes
Create objects.SQL (Creation of tables only)
ei:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Control]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Control](
[ContolName] [nvarchar](50) NULL,
[ControlSchemesName] [nvarchar](50) NULL
) ON [PRIMARY]
END
GO
Stored Procedures
ie
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Get_XXX]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[Get_XXX]
GO
CREATE PROCEDURE [dbo].[Get_XXX]
@Blah uniqueidentifier
AS
BEGIN
SELECT * FROM XXX
END
CreateKeys.SQL (Primary Keys and Indexes)
ie
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Blah]') AND name = N'IX_Blah')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Blah] ON [dbo].blah
(
[BlahID] ASC
)
IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE object_id = OBJECT_ID(N'[dbo].[PK_Blah1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Blah1]'))
ALTER TABLE [dbo].[Blah1] ADD CONSTRAINT [PK_Blah1] PRIMARY KEY CLUSTERED
(
[BlahId] ASC
)
GO
ForeignKeys.SQL (All the foreign keys in the DB
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_XXX_BLAH]') AND parent_object_id = OBJECT_ID(N'[dbo].[XXX]'))
ALTER TABLE [dbo].[XXX] WITH CHECK ADD CONSTRAINT [FK_XXX_BLAH] FOREIGN KEY([XXXID])
REFERENCES [dbo].[BLAH] ([BlahId])
GO
For the most part these are the important scripts
After this it is mostly data. I also have two other scripts. One is used to update structures for versioning. The other maintains the data within the structures. This way people do not need to drop and recreate test data constantly. most people run there DB's to test locally since they only are testing portions of the system. So we have an exe run that just runs my .sql files. All the error handling is done in the .sql files. I make it so that when I put it out to dev everything works.
OK now for my question:
If I have to split these tables up so that security can be used now by other apps and items can be used the same way, but the rest of the 150 tables will be installed with the suite....What is the best way?
I am thinking three options for discussion:
1) All tables are delivered and from this only the tables used are populated. A couple downfalls are from that point everything has to be upgradeable. Management won't understand why we would deliver all these tables. I know the size would be small but delivering all these tables would be tough to maintain with the changes we currently make on a monthly basis.
2) Make Separate scripts that run through SSIS so that I can check for data consistency as I put FK's in place and indexes that are required to be unique by the suite. This is a lot more to maintain. If someone has an example I would appreciate it
3) Break the FK's in the tables and do junction tables in all tables set up for the splits
4) Leave work and never come back.....I do not really like this option
Thanks for the help as always :hehe:
May 15, 2009 at 6:03 am
I'm not sure I got a clear picture of your scenario and issue/s. Perhaps I'm getting to old for this, perhaps is the fact that there is no coffee yet on my system.
If you do not get a spot on answer in the next 48 hours I would suggest to re-work your posting.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.May 15, 2009 at 1:51 pm
ya know I thought about this quite a bit and I think it is just screwed up. I had some coffee and then some tea (thinking tea) and I am going to have to say one script is easier and since there is so much reliance between entities it will be to hard to split.....
how about if I discuss it like this.
SAP... It is considered modular. You have say manufacturing/GL/AP/AR and then you want to bolt on Sales. So when you get SAP it has core tables correct? Then a year goes by and you want the sales module. Are the sales tables a separate entity? Do they only co-exist by the api or does the existing db consume the new module tables? Meaning adding tables, references, and fields required.
Navision on the other side of the scope when you get this you get the whole structure I think, but you are only licensed to use certain portions of the tables. Is that correct. So same scenerio....Is Sales already in the DB and theoretically you just turn on that portion of the app?
May 15, 2009 at 3:03 pm
Hi
I hope I understood your question... :ermm:
It depends (as always).
If your complete application is very large and you are working with different developer teams (maybe in different locations) the separated module installation seems to be a good way to go. You can handle this by schemata. This makes your different teams more flexible and it's more easy to introduce new modules (add-ins on client-side).
If your developers are one team and working in same department on same system the release management should be more easy if you have only one database. The different modules can either be disabled or also introduced on client-side by add-in management.
Greets
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply