Tag: database-design

SQL Design: need ability to add custom “columns” to a table using a fixed schema

Using: SQL Server 2008, Entity Framework, WCF 4 REST I have a table for holding the measurement data generated by monitoring system (aka the app). There are currently about 10 different well-known pieces of data being monitored within the app – and each corresponds to a column in the table. Each customer can “customize” each […]

What's the better way to structure this data within a database?

We’re doing a little redesign and consolidation of some tables in a database. Where we once had 2 tables, ‘administrators’ and ‘users‘ we’re now combining into a single table called ‘users’. To facilitate that change we’ve created a ‘user_types’ table and a ‘user_user_types’ table which is the one-to-many linking table between ‘users’ and ‘user_types’. The […]

What's after Z in SQL_Latin1_General_CP1_CI_AS?

I am trying to prove a table design flaw in a production db, that a table must not have a clustered primary key on a column that can have a random data, in this case a code keyed in by end user. Though we know the solution is to make the PK as non-clustered, I […]

Best pattern for storing (product) attributes in SQL Server

We are starting a new project where we need to store product and many product attributes in a database. The technology stack is MS SQL 2008 and Entity Framework 4.0 / LINQ for data access. The products (and Products Table) are pretty straightforward (a SKU, manufacturer, price, etc..). However there are also many attributes to […]

Grouping by an alias

SELECT COALESCE (rsu.last_name + ‘, ‘ + rsu.first_name + ‘ ‘ + rsu.middle_name + ‘.’, rsu.last_name + ‘, ‘ + rsu.first_name) as student_name, rsu.day_id FROM roster_school_unattended rsu GROUP BY student_name ORDER BY rsu.day_id does not work. What’s the most elegant workaround? EDIT: The result-set should have something like this Muster, Hans | 2011-11-01 Muster, Hans […]

Design principles for designing database architecture of financial transaction system?

I want to design a database which will keep record for financial transaction.I want to design it as a product so that it can be used for any type of financial transaction.Are there some design principles specific to financial transaction database design that can help me out to make database more durable for long term […]

What would be a good datamodel to describe a datamodel?

I’m considering a “simple” project, where I can create a datamodel based on an existing database and store this datamodel inside another database. Consider it a meta-datamodel. Basically, I would have a “Database” table, listing the databases and linked to this the “Table” table listing the tables per database. Then a “Fields” table listing all […]

Why are composite primary keys still around?

I’m assigned to migrate a database to a mid-class ERP. The new system uses composite primary keys here and there, and from a pragmatic point of view, why? Compared to autogenerated IDs, I can only see negative aspects; Foreign keys becomes blurry Harder migration or db-redesigns Inflexible as business change. (My car has no reg.plate..) […]

Change collations of all columns of all tables in SQL Server

I imported a database with some data to compare with another database. The target database has collation Latin1_General_CI_AS and the source database has SQL_Latin1_General_CP1_CI_AS. I did change the collation of the source database in general to Latin1_General_CI_AS using the SQL Server Management Studio. But the tables and columns inside remains with the old collation. I […]

Which SQL Server field type is best for storing price values?

I am wondering what’s the best type for a price field in SQL Server for a shop-like structure? Looking at this overview we have data types called money, smallmoney, then we have decimal/numeric and lastly float and real. Name, memory/disk-usage and value ranges: Money: 8 bytes (values: -922,337,203,685,477.5808 to +922,337,203,685,477.5807) Smallmoney: 4 bytes (values: -214,748.3648 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.