I am forced to manually document a legacy enterprise database. Are there some standard good practices or tools to do this?  To give you some more context, I have recently been given restricted access to a legacy enterprise database. There is very little documentation. I don't have permissions to view system tables or generate ER diagrams. I am exploring/using the database and manually documenting it as I go.   I would like to represent the schema with code (manually) in such a way that it can be parsed, say, with Python, to generate an ER diagram?  Are there tools to do this kind of thing? Or standard good practices to document the schema, annotate definitions and common keys etc?  I'm imagining some kind of a tree or graph like data structure that can be used to encode the schema, parsed with Python etc, and used to generate ERDs, etc, outside of the db management tool; (since I don't have permissions to do this inside the db management tool (Oracle SQL Developer)).
16466
I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.   How do you document your database? (SQL-Server)  What tool do you use?  Documentation Storage Format for database schema/meta-data?   Word documents Excel spreadsheet Plain Text  Documentation process or policies?   I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.
90248
Assuming you can't use LINQ for whatever reason, is it a better practice to place your queries in stored procedures, or is it just as good a practice to execute ad hoc queries against the database (say, SQL Server for argument's sake)?
19523
I'm developing a module that will rely heavily on external database queries. Is there a best practice for defining and using an external database connection throughout a module?    tells me how to make the connection, but not where to put it in a module (specific hook?) so I only have to define it once. Also, is it always necessary to do "db_set_active('YourDatabaseKey');" or can I pass an argument that will set the db to use? I'm using Drupal 7.
15142
note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database.  I have a self-referencing table with a primary key, id and a foreign key parent_id.  +------------+--------------+------+-----+---------+----------------+ | Field      | Type         | Null | Key | Default | Extra          | +------------+--------------+------+-----+---------+----------------+ | id         | int(11)      | NO   | PRI | NULL    | auto_increment |  | parent_id  | int(11)      | YES  |     | NULL    |                |  | name       | varchar(255) | YES  |     | NULL    |                |  | notes      | text         | YES  |     | NULL    |                |  +------------+--------------+------+-----+---------+----------------+   Given a name, how can I query the top-level parent?  Given a name, how can I query all of the id's associated with a record of name = 'foo'?  context: I am not a dba, but am planning to ask a dba to implement this type of hierarchical structure and would like to test some queries. The motivation for doing so is described by .    Here is an example of the relationships among ids in the table:    -- ----------------------------------------------------- -- Create a new database called 'testdb' -- ----------------------------------------------------- SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';  CREATE SCHEMA IF NOT EXISTS `testdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `testdb` ;  -- ----------------------------------------------------- -- Table `testdb`.`observations` -- ----------------------------------------------------- CREATE  TABLE IF NOT EXISTS `testdb`.`observations` (   `id` INT NOT NULL ,   `parent_id` INT NULL ,   `name` VARCHAR(45) NULL ,   PRIMARY KEY (`id`) ) ENGINE = InnoDB;  SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;  -- ----------------------------------------------------- -- Add Example Data Set -- -----------------------------------------------------   INSERT INTO observations VALUES (1,3), (2,5), (3,NULL), (4,10),     (5,NULL), (6,1), (7,5), (8,10), (9,10), (10,3);
105549
Design patterns are usually related to object oriented design. Are there  for creating and programming ? Many problems surely must have reusable solutions.  Examples would include patterns for table design, stored procedures, triggers, etc...  Is there an online repository of such patterns, similar to ?    Examples of problems that patterns could solve:   Storing hierarchical data (e.g. single table with type vs multiple tables with 1:1 key and differences...) Storing data with variable structure (e.g. generic columns vs xml vs delimited column...) Denormalize data (how to do it with minimal impact, etc...)
88340
As a DBA - assuming my users are knowledgeable with PLSQL and SQL - why would I not grant my users privileges to compile and run their own PLSQL code. We have an MI database environment where the data has undergone ETL. The tables contain a large numbers of rows but not necessarily large rows in size. The analysis - will be - I assume of calculus in nature. The users are wanting to run their own code on the data for the purposes of ad-hoc analysis - within their own schemas. They come from a SAS background which from my understanding has allowed them to write and run their own analysis of data. Albeit, SAS runs on its own dataset - extracted from Oracle or elsewhere.  In my opinion this is a dangerous set-up - but without answering my own question - what are the pitfalls?
35716
Through my frustration of not finding the post-installation wizard with ArcSDE 10.1 and being unsuccessful with the command line tool to start an SDE service on our SDE databases, I found I was able to access the databases and copy/past/add features/delete features/etc. all through direct connections to our SQL Server 2012 machine. This was WITHOUT ArcSDE running.   So... The question is, why do I need to go through the trouble of trying to get the SDE services to run if I can manage our databases through the Desktop tools? I was even able to create an enterprise geodatabase and verified that all of the necessary business tables were created. I have even recreated all our map services in ArcGIS Server 10.1 with direct connect and the web editing is perfectly working.   Perhaps I'm missing something...  UPDATE:  As indicated in the  topic:     "The recommended method to access enterprise geodatabases is to   connect directly from ArcGIS clients. If you only use direct   connections to your geodatabase, you do not have to install the ArcSDE   application server."   If you need to edit settings in the ArcSDE system tables, though, you should still install the command line tools included with the application server. I don't think editing system tables directly through SQL is supported technically.
112890
I have always made a point of writing nice code comments for classes and methods with the C# xml syntax. I always expected to easily be able to export them later on.  Today I actually have to do so, but am having trouble finding out how. Is there something I'm missing? I want to go Menu->Build->Build Code Documentation, but there is no option to do that, there.
80168
I have a database file from MongoDB 2.4 and we want use this on mongodb 3.6.  note: the file is original from the db path. Not from mongodump. This process is messy as it requires doing so on many of our clients.  Any advice will be welcomed as I'm a bit lost in this process.  Thanks
6875
I am trying to find some database tutorials,books and similar, since I have started this course in college, and received some material, but it is really hard to manage through all the info on the net. I searched through the questions on the site but didn't find any. Thankfull for all your input.
65064
I'm trying to display all of my corporate database instances and their relationship to each other, so to speak the database links. I found the geometry shape cylinder using TikZ \usetikzlibrary{shapes.geometric}, but I'm new to the whole TikZ stuff and it doesn't worked out that well so I thought maybe you could help me getting something like the following figure:    There will be a ton of instances and arrows, but I hope that you guys could give me a start so that I could do the rest on my own.
41779
I m trying to understand where we use following Database.Methods. I know if we want to insert a record to an object we use DML Statement. What i want to know is why/where we use Database. methods. It might be silly question. Database.saveResults Database.insert Database.query Database.Query Database.error   really appreciate the inputs. Thanks a ton in advance.
5834
Take the representation of an Address, here below is a complete and very detailed implementation:    here instead is a quick implementation (which more or less contains the same fields, imagine all fields in the former are contained also in the second)    If I were to decide which is the closer to a normalized and academically correct one I'd say the first one, however if I were to start a project I would go with the second.  Do you agree with this consideration? And if yes, how does one deal with this fact?   start with an easy database and as soon as it is time improve it into a more normalized/academical database. start with something as close as possible to the academical database stick with the quick and dirty solution
20632
Is there an easy way to chase down table/stored procedure/function dependencies in SQL Server 2005+?  I've inherited a giant application with lots of tables and even more stored procedures and functions that are long and interlinked.    At the end of the day is there a way to build a dependency tree?  Ideally what I'm looking for goes in both directions:  For a table/procedure - what depends ON it?:  Show me all the stored procedures that eventually reference it (ideally in a tree view such that sub procedures nest out to the bigger procedures that call them)  For a procedure - what does IT depend on?:  Show me all the procedures and tables that a given procedure will (or could) touch when running.  It seems this tool shouldn't be that hard to make and would be incredibly useful for DB maintenance generally.  Is anyone aware of such a thing?  If this doesn't exist, why the heck not?  The built-in functionality in Management Studio is nice but the information does not appear to be complete at all.
94234
Window account is getting the below error when trying to connect into the SQL instance via management studio 2014.  The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)  The SQL instance is 2014, the user doesn't get the error if I make the login sysadmin.   The login has connect permission to the instance and even if i give it dbowner to the master database it cannot connect.   Default database is master also.  It is not an orphaned user I have tried the GRANT VIEW DEFINITION - no luck I have checked and the login doesnt have any DENY permissions   When i go into the properties of the login > Securables > effective tab > it gives the error:  The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.  So that is obviously related also   Any ideas?
49743
If we have to store the available positions at a company (i.e. Manager, Team Lead, ... etc). What are the best practices for storing it? I have two opinions with comments... "sure, welcoming yours"        Storing it as DB table with columns ID and Name, and deal with it using queries and joins.     Storing it as Enum and forget about the DB table.    In my opinion, I will choose the first solution if I have changing items. So that I won't hard code these options as Enum. I may choose the Enum solution, if I have no doubt that data won't change (for example, Gender: Male, Female).  NOTE: I code in English, and the UI Culture may be Arabic. If I will work with the Enum Solution, I will hard code the culture-based strings in the presentation layer, is it okay from the best practices perspective!!!!  I would like to know your opinions and if my thoughts correspond to what is most recommended "Best Practices"??
81313
I'm starting a new application and looking at using an ORM -- in particular, SQLAlchemy.  Say I've got a column 'foo' in my database and I want to increment it.  In straight sqlite, this is easy:  db = sqlite3.connect('mydata.sqlitedb') cur = db.cursor() cur.execute('update table stuff set foo = foo + 1')   I figured out the SQLAlchemy SQL-builder equivalent:  engine = sqlalchemy.create_engine('sqlite:///mydata.sqlitedb') md = sqlalchemy.MetaData(engine) table = sqlalchemy.Table('stuff', md, autoload=True) upd = table.update(values={table.c.foo:table.c.foo+1}) engine.execute(upd)   This is slightly slower, but there's not much in it.  Here's my best guess for a SQLAlchemy ORM approach:  # snip definition of Stuff class made using declarative_base # snip creation of session object for c in session.query(Stuff):     c.foo = c.foo + 1 session.flush() session.commit()   This does the right thing, but it takes just under fifty times as long as the other two approaches.  I presume that's because it has to bring all the data into memory before it can work with it.  Is there any way to generate the efficient SQL using SQLAlchemy's ORM?  Or using any other python ORM?  Or should I just go back to writing the SQL by hand?