Tag: database-design

Can a Primary Key be a Non Clusterd Index on a Clustered Table?

I am designing a Members Table to store the users of a website. It will be used every time a user logs on to the website and occasionally accessed to update user details. The users will log on with an email address and password and every account will have a unique email address. Therefore it […]

Database Trigger Usage – Best Practices

Brief Background: I currently maintain a fifteen year old inventory management system. In an effort to provide real-time quantity on hand for both single items and kits (possibly nested down to the Nth level), inventory is allocated to orders on the fly when a page is requested (allocation priority is determined by date received and […]

Can a SQL Server database schema always enforce its application's business logic by the use of foreign keys and check constraints alone?

Thank you for your earlier answers, but based on their feedback I have reformed the question. If the answer to the question is no, then can the data’s integrity for enforced by any other means. I do not consider the use of stored procedures sufficient as they may be circumvented. Would triggers be required?

School Database Design Required

I am planning to build a database for a school application. I have designed a database to hold time tables for all the classes. It should also relate to the teachers taking the subject for the class. Also should be able to handle a public holidays mentioned. Also the attendance of all the students and […]

One-to-one relationship or One-to-many?

Maybe I need more coffee this morning but here goes… I have a very simple inventory system. Right now I have two tables: Items and Inventory. Items Id Title YearReleased Inventory Id ItemId(Foreign key to Items) Quantity QuantityOnHand Each item has one inventory and each inventory belongs to one item. The relationship between the two […]

Is it possible to do a left outer join where something other than NULL fills the non-values?

Here’s the problem. 🙁 A column of my table Answers which is like Answers ———————————— id | user_id | question_id | ans ———————————— 1 | 1 | 1 | 0 2 | 1 | 2 | 85 3 | 2 | 1 | 5 4 | 2 | 2 | NULL where the value of […]

Teradata equivalent of persisted computed column (in SQL Server)

We have a few tables with persisted computed columns in SQL Server. Is there an equivalent of this in Teradata? And, if so, what is the syntax and are there any limitations? The particular computed columns I am looking at conform some account numbers by removing leading zeros – an index is also created on […]

How to record different authentication types (username / password vs token based) in audit log

I have two types of users for my system, normal human users with a username / password, and delegation authorized accounts through OAuth (i.e. using a token identifier). The information that is stored for each is quite different, and are managed by different subsytems. They do however interact with the same tables / data within […]

SQL Server field calculation based on multiple condition

Here is my scenario: I have a Person table with following fields. create table Person(PersonID int primary key identity(1,1), Age int, height decimal(4,2), weight decimal(6,2) ); insert into Person(Age,height,weight) values (60,6.2,169); — 1 insert into Person(Age,height,weight) values (15,5.1,100); — 2 insert into Person(Age,height,weight) values (10,4.5,50); — 3 What I need to do is, if the […]

DataTables and SQL and…god knows what else

WARNING: Long and complicated question… So here’s the story so far: I’m trying to wean the sound & lighting hire company I work for off their Excel/VBA based quoting/specification system; I’m in the process of developing an SQL Server/C# Winforms solution. The problem I’m running into is the Specification side of it. In the old […]

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