I infrequently (monthly/quarterly) generate hundreds of Crystal Reports reports using Microsoft SQL Server 2005 database views. Are those views wasting CPU cycles and RAM during all the time that I am not reading from them? Should I instead use stored procedures, temporary tables, or short-lived normal tables since I rarely read from m...
Does anyone know of papers/books/etc. that document patterns for databases? For example, one common rule of thumb is that every table should have a primary key and that the key should be devoid of information content. So I was wondering if anyone had written a book or published papers regarding design patterns for designing relational ...
Example I have Person, SpecialPerson, and User. Person and SpecialPerson are just people - they don't have a user name or password on a site, but they are stored in a database for record keeping. User has all of the same data as Person and potentially SpecialPerson, along with a user name and password as they are registered with the sit...
Does someone have any tips/advice on database design for a web application? The kind of stuff that can save me a lot of time/effort in the future when/if the application I'm working on takes off and starts having a lot of usage. To be a bit more specific, the application is a strategy game (browser based, just text) that will mostly inv...
When creating a database structure, what are good guidelines to follow or good ways to determine how far a database should be normalized? Should you create an un-normalized database and split it apart as the project progresses? Should you create it fully normalized and combine tables as needed for performance? ...
How would you design a database to support the following tagging features: items can have a large number of tags searches for all items that are tagged with a given set of tags must be quick (the items must have ALL tags, so it's an AND-search, not an OR-search) creating/writing items may be slower to enable quick lookup/reading Idea...
I am designing a contact management system and have come across an interesting issue regarding modeling geographic locations in a consistent way. I would like to be able to record locations associated with a particular person (mailing address(es) for work, school, home, etc.) My thought is to create a table of locales such as the followi...
varchar(255), varchar(256), nvarchar(255), nvarchar(256), nvarchar(max), etc? 256 seems like a nice, round, space-efficient number. But I've seen 255 used a lot. Why? What's the difference between varchar and nvarchar? ...
I'm creating an ASP.NET web site where all pages hang off a database-driven tree-hierarchy. Pages typically present HTML content. But, some will execute programming. Examples: (1) a "contact us" form or (2) a report generator How should I represent/reference the programming within the database? Should I have a varchar value of a Web...
I'm creating a web site where all pages hang off a database-driven tree-hierarchy. All but one node has a parent node. Nodes may have role-based read permissions. Some nodes may have special rules (such as: don't display within navigation menus). Nodes may represent links to other nodes (like a shortcut in Windows). Nodes typically ...
Let's say I've got Alpha things that may or may not be or be related to Bravo or Charlie things. These are one-to-one relationships: No Alpha will relate to more than one Bravo. And no Bravo will relate to more than one Alpha. I've got a few goals: a system that's easy to learn and maintain. data integrity enforced within my databas...
When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it? This probably applies to other databases as well. I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully. ...
A former coworker insisted that a database with more tables with fewer columns each is better then one with fewer tables with more columns each. For example rather than a customer table with name, address, city, state, zip, etc. columns, you would have a name table, an address table, a city table, etc. He argued this design was more ef...
If you're familiar with the phrase "build one to throw away", well, we seem to have done that; we’re reaching the limits of version 1 of our online app. It's time to clean things up by: Re-organizing code and UI Unifying UI processes Adding more functionality Building for the future Modifying our database structure to handle all of the...
Here is an example of what I've got going on: CREATE TABLE Parent (id BIGINT NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB; CREATE TABLE Child (id BIGINT NOT NULL, parentid BIGINT NOT NULL, PRIMARY KEY (id), KEY (parentid), CONSTRAINT fk_parent FOREIGN KEY (parentid) REFERENCES Parent (id) ON DELETE CASCADE) ENGINE=InnoDB; CREAT...
What is the best way to handle user account management in a system, without having your employees who have access to a database, to have access to the accounts. Examples: Storing username/password in the database. This is a bad idea because anyone that has access to a database can see the username and password. And hence use it. Sto...
We are rewriting our legacy Accounting System in VB.NET and SQL Server. We brought in a new team of .NET/ SQL Programmers to do the rewrite. Most of the system is already completed with the Dollar amounts using Floats. The legacy system language, I programmed in, did not have a Float so I probably would have used a Decimal. What is your...
Here we go again, the old argument still arises... Would we better have a business key as a primary key, or would we rather have a surrogate id (i.e. an SQL Server identity) with a unique constraint on the business key field? Please, provide examples or proof to support your theory. ...
Say you have an entity like a vehicle that you are capturing detailed information about. The car you want to capture is painted red, black and white. The front tires are Bridgestone 275/35-18 and the rear tires are 325/30-19. And sometimes you can have just two tires (yes this would be considered a motorcycle which is a type of vehicle) ...
What is a recommended architecture for providing storage for a dynamic logical database schema? To clarify: Where a system is required to provide storage for a model whose schema may be extended or altered by its users once in production, what are some good technologies, database models or storage engines that will allow this? A few p...