I've searched a bit and didn't see any similar question, so here goes. How do you know when to put an index in a table? How do you decide which columns to include in the index? When should a clustered index be used? Can an index ever slow down the performance of select statements? How many indexes is too many and how big of a table do...
I have some really good books for SQL Server, like: SQL Server 2008 Bible Pro SQL Server 2008 - Relational Database Design and Implementation SQL Server 2008 for Developers. Can you suggest/recommend some other titles, that may address other topics perhaps, that you found truly useful? ...
I am looking for some help with designing some functionality in my application. I already have something similar designed but this problem is a little different. Background: In my application we have different Modules. Data in each module can be associated to other modules. Each Module is represented by an Object in our application....
Hey everyone, I have this question that keeps coming up over and over in terms of best practices. Lets say I have the following table: [Process] ---------------- ProcessID int ProcessName varchar(100) ... In addition to the fields above, lets say a [Process] can have various states. It can be currently successful, currently unsuccess...
What is the best way to create this data structure: Key - field1- field2 - ... fieldn. So every record can have a key and then multiple values following that key .. like for eg: key is the employee id and fields are name age salary .. So basically something like a database table but want to do it using a data structure such as hashtable...
Just like stackoverflow, there is a many-to-many relationship between Question and Tag. After running these symfony commands: ./symfony doctrine:drop-db ./symfony doctrine:build-db ./symfony doctrine:build-model ./symfony doctrine:build-sql ./symfony doctrine:insert-sql With the following schema: schema.yml Tag: columns: name: ...
Ok, so I want to create a database with all the data of the site. This is no problem however, for those items that exist only once, what should I do? For instance, on the homepage, the intro text is changed by a user frequently, how to store this in the db? There are 6 additional items on the homepage that are change frequently by the u...
We're building a simple web based system whereby somebody adds a record, a CMS page for example, that gets approved by someone in charge before being shown on website. If the author then decides to edit that page later, we want to create a draft based on the live copy, on approval it will replace the old live page. We thought about doi...
I've been working on a web application that is basically a CRUD application (Create, Read, Update, Delete). Recently, I've started working on what I'm calling an "approval workflow". Basically, a request is generated for a material and then sent for approval to a manager. Depending on what is requested, different people need to approve t...
I would like to automate SchemaSpy as part of a nightly build against a SQL Server database. I am unsure exactly how to go about this as there seems to be java sql server drivers missing when I go to generate. Could anyone give me a step by step run through of how to run schemaspy in this context please? ...
After running the following SQL statements, you will see that, MySQL has automatically created the non-unique index question_tag_tag_id_tag_id on the tag_id column for me after the first ALTER TABLE statement has run. But after the second ALTER TABLE statement has run, I think MySQL should also automatically create another non-unique in...
Modeling tool or just a visual tool for drawing. In fact I only need to draw and later I'll transform the picture into sql by hand to better control the process. ...
In my web application, my users have many events. One such event is "user updated facebook status." A user could have hundreds of that type of event, and there are 10 types of events. I need to display event counts and other user statistics based on events in a very scalable manner. This is because each user will be able to see his o...
I have a table with two child tables. For each record in the parent table, I want one and only one record in one of the child tables -- not one in each, not none. How to I define that? Here's the backstory. Feel free to criticize this implementation, but please answer the question above, because this isn't the only time I've encountered...
Do you ever use a separate table for "generating" artificial primary keys for DB (and why)? What I mean is to have a table with two columns, table name and current ID - with which you could get new "ID" for some table by simply locking the row with that table name, getting the current value of the key, increment it by one, and unlock the...
Dear All, I am new to MySql database. I've large table(ID,...). I select ID frequently with java code and.And that make a heavy load on transaction select from tableName where ID=someID notes: 1.Database could be 100,000 records 2.I can't cache result 3.ID is a primary key 4.I try to optimize time needed to return result from que...
What's the best practice or well known methods to implement sequence numbers for business entities such as invoices, purchase orders, job numbers, etc? I want to be able to save the latest value in the database and be able to set it programatically. Is it OK to use a table for this purpose that has a SEQUENCE_NAME, SEQUENCE_NUMBER tuple?...
Originally I had two tables in my DB, [Property] and [Employee]. Each employee can have one "Home Property" so the employee table has a HomePropertyID FK field to Property. Later I needed to model the situation where despite having only one "Home Property" the employee did work at or cover for multiple properties. So I created an [Emp...
Target application is a medium-sized website built to support several hundred to several thousand users an hour, with an option to scale above that. Data model is rather simple, and caching potential is pretty high (~10:1 ratio of read to edit actions). What should be the considerations when coming to choose between a relational, SQL-ba...
By fiscal year I mean all the data in the database (in all tables) that occurred in the particular year. Lets say that we are building an application that allows user to choose from different years. What way of implementing this would you prefer, and why: Separate fiscal year data based on multiple separate database instances (for exa...