I'm considering the best way to design a permissions system for an "admin" web application. The application is likely to have many users, each of whom could be assigned a certain role; some of these users could be permitted to perform specific tasks outside the role. I can think of two ways to design this: one, with a "permissions" tabl...
I'm trying to design an application to hold academic reference information. The problem is that each different type of reference (eg. journal articles, books, newspaper articles etc) requires different information. For example a journal reference requires both a journal title and an article title, and also a page number, whereas a book ...
An answer to a question of mine on DB design suggested something called single table inheritance. I've done a bit of searching on that, but I can't seem to find that much clear information on it. Basically what I seem to understand from it is that you have a big table with all the fields in it and also a type field - and then your ORM l...
i need to create a database table to store different change log/auditing (when something was added,deleted,modified,etc). i dont need to store particularly detailed info, so i was thinking something along the lines of: id (for event) user that triggered it event name event description timestamp of the event am i missing something her...
I want to just store the year in one of my models. At the moment I just have a database field called year of type date, but it seems to want to take a whole date (yyyy-mm-dd) rather than just a year. What's the best way to store this? In a date field but using some way of just getting it to store the date bit (in which case, how?), or i...
What do I have to consider in database design for a new application which should be able to support the most common relational database systems (SQL Server, MySQL, Oracle, PostgreSQL ...)? Is it even worth the effort? What are the pitfalls? ...
I have a web-based application that notifies users of activity on the site via email. Users can choose which kinds of notifcations they want to receive. So far there are about 10 different options (each one is a true/false). I'm currently storing this in one varchar field as a 0 or 1 separated by commas. For example: 1,0,0,0,1,1,1,1,0,0...
I have a database that contains a table that looks a bit like this: PropertyId, EntityId, Value PropertyId and EntityId are a combined primary key. Every Entity is spread over a couple of rows where every row contains a single property of the entity. I have no control over this database so I'll have to work with it. Is it possible to ...
I need a data structure that can store non-overlapping ranges within a single dimension. The entire range of the dimension need not be completely covered. An example would be a conference room scheduler. The dimension is time. No two schedules may overlap. The conference room isn't always scheduled. In other words, for a given time ther...
Need to calculate a stock market portfolio based on trades in a database. The calculation I'm looking to do via SQL are average price, rate of return, portfolio value, etc. Sample data: Stock Shares Price Value A 100 50 5000 A -20 60 -1200 A 50 40 2000 I haven't been able to do it with SQ...
A friend of mine claims that in a typical database, using (for example) nvarchar[256] will give marginally better performance than nvarchar[200] or nvarchar[250] because of the granularity of page allocations. Is there any truth to this whatsoever? Thanks! ...
I'm looking for a strategy in as far as stopping the repetitive problem of branching out tables. For example as a fictitious use case, say I have a table with users that contains their name, login, password and other meta data. In this particular scenario, say the user is restricted to login per a specific subset of IP(s). Thus, we have ...
I'm designing a database for an events management site - there is a table of venues and a table of events. Each event is in a venue (stores the venue's id) and each venue is in a city. It must be possible to search for event by city, should city be a field in the venues table (which could possibly result in duplicate cities due to misspe...
Can i have multiple primary keys in a single table? ...
Previously, settings for deployments of an ASP.NET application were stored in multiple configuration files under the Web.config config sections using a KEY/VALUE format. We are moving these 'site module options' to the database for a variety of reasons. Here are the two options we are mulling over at the moment: 1. A single table with...
In one of the stackoverflow podcasts (#18 I think) Jeff and Joel were talking about multi vs single tenant databases. Joel mentioned that "FogBugz on Demand" used a database per customer architecture and I was wondering if there is a point beyond which you'll need to have multiple database servers to distribute the load? ...
As above. Usually at work someone else does the database design, and when I do personal projects they're small enough to not really require more than five or six tables, but I'm about to start a personal project that I think will probably require around eleven or twelve tables and I'd like something to help me design it neatly and keep t...
What would be the most efficient data type to store a UUID/GUID in databases that do not have a native UUID/GUID data type? 2 BIGINTs? And what would be the most efficient code (C# preferred) to convert to and from a GUID to that type? Thanks. ...
Hi. I have some products that belongs to the some category. Each category can have different properties. For example, category cars has properties color, power, ... category pets have properties weight, age, ... Number of categories is about 10-15. Number of properties in each category is 3-15. Number of products is very big. M...
This is a simplified version of the problem. We have customers who send us lots of data and then query it. We are required by them to have several "public" ids they can query our data by. (Most want to query our system via the id they send along with the data, but not always). For simplicity, we'll call them "pid", "crid" and "music...