A Database with 70 Table

  • hi everybody

    I Have Database With 70 Tables and this my first time that i have alot table in my DB. I want Any Subject or Topic that is Good for me With This situation Secuirity or optimization or etc.

  • Wow, large subject area there. Maybe start with a couple of questions about the key things you want to find out about. The articles on the left there is probably a good start

  • That is a very small database. Wait until you work with a system like PeopleSoft with 50,000+ tables. Good thing the application handles the security there.

  • That is not a large topic, it is more like I have lost a grain of sand somewhere in the Milky Way, how do I find it? There are hundreds and hundreds of entire books on the topics you mentioned. Just a general shotgun blast approach isn't going to gain you much insight. Are you a dba by choice or an "accidental dba"? The direction depends greatly on your experience and knowledge of computer systems, networks, sql server etc. And fwiw 70 tables is quite a small db. Best thing is to figure out a specific topic and research it.

    --edit--

    Lysdexia strikes again.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • vahid.arr (9/18/2012)


    hi everybody

    I Have Database With 70 Tables and this my first time that i have alot table in my DB. I want Any Subject or Topic that is Good for me With This situation Secuirity or optimization or etc.

    70 tables isn't necessarily a lot. I realize it's large to you however, so I'm not dissing it, but it's really just the tip of the iceberg.

    What's going to interest you primarily here is joins and query optimization for starters. The security and maintenance should be in the hands of the primary DBA, and you can use him as a resource for that. You'll initially want to investigate indexes, data normalization, and other basics of DB design. There's a series of stairway articles you'll see in the left navigation panel when you're looking at this under 'Stairways'. This is an introduction series to all sorts of different areas of databasing.

    Welcome to the neighborhood. First step in a long road and all that jazz. If you have particular questions we'll help you find answers, but you're going to have to self-train a *lot*. I particularly recommend you pickup a book for SQL Server, preferably in whatever version you're running at work. Make sure it discusses normalization, table structure, views, query building, optimization, and general maintenance. Most basic books for that will cover those topics.

    If you'll also notice, on the left, there's a 'books' section. These are FREE e-books. Troubleshooting SQL Server: A guide for the Accidental DBA is an excellent resource to get yourself started. You won't necessarily understand everything at first, but it'll show you where a lot of the trouble spots are to get yourself going on.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • vahid.arr (9/18/2012)


    hi everybody

    I Have Database With 70 Tables and this my first time that i have alot table in my DB. I want Any Subject or Topic that is Good for me With This situation Secuirity or optimization or etc.

    Heh... it's easy... deny privs to everyone except to execute stored procedures while this DB is still small enough to "take control" like this. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Well Thanks to all this are the things that i have doubt about it.specialy number 4:

    1- i have table with 40 fields that it have 10 image Type field (varbinary(max)).image SIZE are between 15MB to 100mb

    2-i have some tables that have many to many relationship i use table between them(junction table).how can i speed up them?i mean wheter or not use index in junction table for speed up?

    3-what is deferent between unique constraint and uniqe index ?

    4- i have tables that have relationship together and have one field in both them.is this happen in DB design?

  • vahid.arr (9/19/2012)


    Well Thanks to all this are the things that i have doubt about it.specialy number 4:

    1- i have table with 40 fields that it have 10 image Type field (varbinary(max)).image SIZE are between 15MB to 100mb

    What is the question here. Seems a bit overkill to have 40 varbinary(max) columns in the same table. I suspect normalization would help here but with no more details it is impossible to tell.

    2-i have some tables that have many to many relationship i use table between them(junction table).how can i speed up them?i mean wheter or not use index in junction table for speed up?

    I assume you must have queries that are running slow? There is no magic go fast button or indexing scheme that will work for everything.

    3-what is deferent between unique constraint and uniqe index ?

    They are very similar. This should explain it better than I can. http://msdn.microsoft.com/en-us/library/ms187019.aspx

    4- i have tables that have relationship together and have one field in both them.is this happen in DB design?

    [/quote]

    I am not sure I am following your question. It sounds like you have some foreign keys perhaps?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • What is the question here. Seems a bit overkill to have 40 varbinary(max) columns in the same table. I suspect normalization would help here but with no more details it is impossible to tell.

    Attention I Have A table with 40 field that 10 of them is image type (varbinary(max)).

    I am not sure I am following your question. It sounds like you have some foreign keys perhaps?

    i have a table with initial Info and Table that every month become update with new info in this tables i Have Two fields that are Same i cant change it. is this happen in DB Design?

  • I am not sure I am following your question. It sounds like you have some foreign keys perhaps?

    i have a table with initial Info and Table that every month become update with new info in this tables i Have Two fields that are Same i cant change it. is this happen in DB Design?

    Please provide DDL of tables.

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

  • Simply repeating the question is not helping me understand what you are asking. It seems the English is not your first language, no problem, we can work with that. Can you try to reword your question so I have an idea of what you are asking?

    Attention I Have A table with 40 field that 10 of them is image type (varbinary(max)).

    What is this asking?

    i have a table with initial Info and Table that every month become update with new info in this tables i Have Two fields that are Same i cant change it. is this happen in DB Design?

    Without any more information I have no idea what the question is. Do you mean you have two columns with the same in different tables? Not sure what you mean by does this happen in DB Design.

    There seems to be several people in here willing to help but we don't know what the question is.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I solve All Of them Except This one :

    I have Table with 40 column that 10 column of that table is Image Type(varbinary(max)) and my question is that:

    is it not better to use filestream because my image size is between 15MB to 1000MB?

    I enable filestream of sql and i dont know how to convert this 10 column to filestream.this is query of my table .

    plz help me

    CREATE TABLE [dbo].[MantagheInfo](

    [MantagheInfoID] [int] IDENTITY(1,1) NOT NULL,

    [MantagheID] [smallint] NOT NULL,

    [EndUserID] [nvarchar](15) NOT NULL,

    [MizanTadghighMarz] [decimal](12, 6) NULL,

    [MizanTadghighMarzImg] [varbinary](max) NULL,

    [MizanTasbitMarz] [decimal](12, 6) NULL,

    [MizanTasbitMarzImg] [varbinary](max) NULL,

    [VosatPosheshMarta] [decimal](12, 6) NULL,

    [VosatPosheshMartaImg] [varbinary](max) NULL,

    [VosatPosheshJangle] [decimal](12, 6) NULL,

    [VosatPosheshJangleImg] [varbinary](max) NULL,

    [VosatKohestan] [decimal](12, 6) NULL,

    [VosatKohestanImg] [varbinary](max) NULL,

    [VosatDasht] [decimal](12, 6) NULL,

    [VosatDashtImg] [varbinary](max) NULL,

    [VosatTarhJangal] [decimal](12, 6) NULL,

    [VosatTarhJangalImg] [varbinary](max) NULL,

    [VosatMantaghe] [decimal](12, 6) NULL,

    [VosatMantagheImg] [varbinary](max) NULL,

    [VosatAraziMoghofi] [decimal](12, 6) NULL,

    [VosatAraziMoghofiImg] [varbinary](max) NULL,

    [VosatAraziKharidariShode] [decimal](12, 6) NULL,

    [VosatAraziKharidariShodeImg] [varbinary](max) NULL,

    [SanadMalakiat] [nvarchar](500) NULL,

    [VahedHefazatiType] [bit] NULL,

    [TedadMohitBan] [int] NULL,

    [TedadKarshenas] [int] NULL,

    [TedadDorbinMadarBaste] [int] NULL,

    [TedadMotorcycle] [int] NULL,

    [TedadGhayegh] [int] NULL,

    [TedadDoorbinAks] [int] NULL,

    [TedadDoorbinFilm] [int] NULL,

    [TedadDoorbinCheshmi] [int] NULL,

    [TedadBisimSabet] [int] NULL,

    [TedadBisimDasti] [int] NULL,

    [TedadBisimKhodroe] [int] NULL,

    [TedadAslahe] [int] NULL,

    [TedadGPS] [int] NULL,

    [TarikhcheHefazat] [nvarchar](2000) NULL,

    [TedadPestandaran] [int] NULL,

    [TedadParandeghan] [int] NULL,

    [TedadKhazandeghan] [int] NULL,

    [TedadDozistan] [int] NULL,

    [TedadMahiVaAbzian] [int] NULL,

    [TedadGiahan] [int] NULL,

    [TedadBimohreghan] [int] NULL,

    [MantagheInfoSabt] [datetime] NOT NULL,

    [MantagheInfoEdit] [datetime] NULL,

    CONSTRAINT [PK_MantagheInfo] PRIMARY KEY CLUSTERED

    (

    [MantagheInfoID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[MantagheInfo] WITH CHECK ADD CONSTRAINT [FK_MantagheInfo_EndUser] FOREIGN KEY([EndUserID])

    REFERENCES [dbo].[EndUser] ([EndUserID])

    GO

    ALTER TABLE [dbo].[MantagheInfo] CHECK CONSTRAINT [FK_MantagheInfo_EndUser]

    GO

    ALTER TABLE [dbo].[MantagheInfo] WITH CHECK ADD CONSTRAINT [FK_MantagheInfo_Mantaghe] FOREIGN KEY([MantagheID])

    REFERENCES [dbo].[Mantaghe] ([MantagheID])

    GO

    ALTER TABLE [dbo].[MantagheInfo] CHECK CONSTRAINT [FK_MantagheInfo_Mantaghe]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_MizanTadghighMarz] DEFAULT ((0)) FOR [MizanTadghighMarz]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_MizanTasbitMarz] DEFAULT ((0)) FOR [MizanTasbitMarz]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatPosheshMarta] DEFAULT ((0)) FOR [VosatPosheshMarta]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatPosheshJangle] DEFAULT ((0)) FOR [VosatPosheshJangle]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatKohestan] DEFAULT ((0)) FOR [VosatKohestan]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatDasht] DEFAULT ((0)) FOR [VosatDasht]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatTarhJangal] DEFAULT ((0)) FOR [VosatTarhJangal]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_VosatMantaghe] DEFAULT ((0)) FOR [VosatMantaghe]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadMohitBan] DEFAULT ((0)) FOR [TedadMohitBan]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadKhodro] DEFAULT ((0)) FOR [TedadDorbinMadarBaste]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadMotorcycle] DEFAULT ((0)) FOR [TedadMotorcycle]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadGhayegh] DEFAULT ((0)) FOR [TedadGhayegh]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadDoorbinAksbardari] DEFAULT ((0)) FOR [TedadDoorbinAks]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadDoorbinCheshmi] DEFAULT ((0)) FOR [TedadDoorbinCheshmi]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadBisim] DEFAULT ((0)) FOR [TedadBisimSabet]

    GO

    ALTER TABLE [dbo].[MantagheInfo] ADD CONSTRAINT [DF_MantagheInfo_TedadAslahe] DEFAULT ((0)) FOR [TedadAslahe]

    GO

  • Lynn Pettis (9/18/2012)


    That is a very small database. Wait until you work with a system like PeopleSoft with 50,000+ tables. Good thing the application handles the security there.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply