Hi, I have a tricky problem that I've been messing about with for a few days now and cant find an optimal solution for. These are my tables: site site_node page The site node table contains a list of nodes representing a hierarchy (using nested set). Each node must have one or more associated pages. Each site must have one associate...
Rules like "A parent object will have up to 2 children" could be enforced in database using triggers. But would it be a good idea to duplicate the rule if this rule is already enforced in the domain layer. In which cases duplication of such rules are justified? Are there any alternative to avoid such duplication? Is it not a data integ...
I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this without storing more info than I actually need? ...
My software went in production some days ago and now I want to argue a bit about the database structure. The software collects data about ships, currently 174 details for each ship, each detail can be a text value, a long text value, a number (of a specified length, with or without a specified number of decimals), a date, a date with ti...
I'm working on a fairly simple survey system right now. The database schema is going to be simple: a Survey table, in a one-to-many relation with Question table, which is in a one-to-many relation with the Anwser table and with the PossibleAnwsers table. Recently the customer realised she wants the ability to show certain questions only...
We're taking one of our production databases and creating a copy on another server for read-only purposes. The read-only database is on SQL Server 2008. Once the database is on the new server we'd like to optimize it for read-only use. One problem is that there are large amounts of allocated space for some of the tables that are unuse...
So, I have a table where StartDate and EndDate's are used to determine the activeness of a record. I thought of using NULLs to relieve the maintainers from having to manufacture some crazy dates for some of the records. For example, if NULL had defined as positive infinite when used in EndDate, the data maintainers wouldn't need to come ...
I’ve got a table: CA ca_id placement_id blah blah This table gets updated with new unique placements every hour from data warehouse. Now the end-user wants to track statuses for those placements. I was thinking of adding a new table which would get updated hourly as well: CA_Status placement_id record_status last_updated_on O...
I've heard reasoning behind this and I was curious if others think of this as a best practise/good idea. One reasoning is, that putting restrictions on direct access to database tables, and forcing apps/users to use SPs (Stored Procedures) to do CRUD operations will allow DBAs fine grained control to approve/review SPs before moving ...
Hey all, my site is now going to start allowing two different types of users to sign up... normal people companies the two will be able to do all the same things on the site, but their profiles will look different, and im sure they will want to store different types of information about themselves. my question is: should i sto...
My project requires users to create some basic math functions, I need to store these in a ms sql databse to use on datasets in my business logic. The functions will be similar to: when (value1 between 20 and 40) then value2 = value 2 * 37.8 What is the best schema to store something like this ? And what is the most efficient way...
Reference Data, or lookup tables are things like CustomerType, ProductType and so on. They change infrequently, occasionally a new type is added or an old one retired. In code they're often reproduced as enums/constants, and also used to populate combo boxes. Adding a new Type shouldn't break existing applications, and more often than no...
I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it comes to dealing with a reasonably sized SQL Server database (anything larger than Northwind or AdventureWorks) - do you use multiple filegrou...
Is there a tool that directly maps XML structure to database schema? The reason I ask this is because my data is best described in terms of XML structure. However, I want to store them in database for fast query result and other benefits. ...
I have to estimate the data size of database when populated with forecast values for each table. It should at least include size to be used for data and indexes. There used to be tool like this in SQL Server 2000 that produced an estimated size when supplied with the amount of records per table (I think it shipped with the SQL 2000 res...
I have an app which has tasks in it and you can reorder them. Now I was woundering how to best store them. Should I have a colomn for the ordernumber and recalculate all of them everytime I change one? Please tell me a version which doesn't require me to update all order numbers since that is very time consuming (from the executions poin...
I've been playing around lately with SQL Data Services. Although (or perhaps because) I can knock out a well-structured relational database in my sleep, I'm struggling to get my head round how to design a performant database in an environment that has (for example) no enforcement of referential integrity and no indexes on columns other t...
I have a database that uses natural keys (i.e. business oriented keys). Unfortunately, because of this, the primary keys for these objects may change over time. I am currently researching the use of NHibernate for an O/RM for this database. However, in my testing I have noticed that there is no apparent way to change the primary key of...
I'm a newbie in Database design and in Hibernate too. I started reading the documentation for Hibernate. It talked about "Collection Mapping", "Association Mapping" and "Component Mapping". I am not understanding the difference between them and not sure about when to use what in one-to-many/many-to-one/many-to-many relationships. To me, ...
When I try to insert a new record into the database using SQLAlchemy and I don't fill out all values, it tries to insert them as "None" (instead of omitting them). It then complains about "can't be null" errors. Is there a way to have it just omit columns from the sql query if I also omitted them when declaring the instance? ...