Tag: database-design

SQL Server database design users, groups, roles, memebers

I have the following SQL Server db with one table so far. —————- |Users | —————- | UserId PK | | | | Other fields | —————- I need to add few more tables to it which is not a problem but this is the goal: —————- —————- —————- —————- |Users | |Roles | | […]

Creating object counters in Entity Framework and Sql Server

Note 1: I REPHRASED THE QUESTION. It now consists of Suppliers and Orders, instead of Cars and Parts. Note 2: THIS PROBLEM IS HYPOTHETICAL. My goal is to understand how to create object counters. For regulatory requirements, I need TO SEQUENTIALLY NUMBER EACH Order for each of the suppliers. I’m Using ‘Entity Framework` with Sql […]

Sql Server row size limit and table design

I have this query on SQL Server 2008 CREATE TABLE MediaLibrary ( MediaId bigint NOT NULL IDENTITY (1, 1), MediaTypeId smallint NOT NULL, ImageNameByUser nchar(100) NULL, GeneratedName uniqueidentifier NOT NULL, UploadedByUserId uniqueidentifier NOT NULL, UploadedDate date NOT NULL, ProfilePhoto bit NOT NULL, PublicPhoto bit NOT NULL, AppointmentId bigint NULL, OriginalImage nchar(1000) NULL, ThumbImage nchar(1000) NULL, […]

How to choose indexes for chat application database

I work on a small chat application with database. I create the database shown in the image. I new to indexes and I want to choose the appropriate indexes for the query. Can I use clustered index in the Messages table ? and if I can, Which column (or columns) should have clustered index ? […]

When are nulls “safe” in a column?

Is there a general rule of thumb for designing databases to allow the columns as nulls vs 3nf normalization? I have a table with a column that will mostly consist of nulls (85%) but the table size is not over 10K records (not very big). It’s mostly for logging and record keeping so most transactions […]

Is it a good practise to have circular reference in two DB tables

In our DB we have two tables A, B with primary keys A_id and B_id. Is it a considered a good practice to have B_id as foreign key in table A and A_id as foreign key in table B. This would allow us to have many-to-many relationship in the table. An alternative would be to […]

How BIG do you make your Nvarchar()

When designing a database, what decisions do you consider when deciding how big your nvarchar should be. If i was to make an address table my gut reaction would be for address line 1 to be nvarchar(255) like an old access database. I have found using this has got me in bother with the old […]

SQL Server – Clustered index design for dictionary

Would like some advice from this. I got a table where I want to keep track of an object and a list of keys related to the object. Example: OBJECTID ITEMTYPE ITEMKEY ——– ——– ——- 1 1 THE 1 1 BROWN 1 2 APPLE 1 3 ORANGE 2 2 WINDOW Both OBJECTID and ITEMKEY have […]

PostgreSQL analog of SQL Server index

Trying to recreate my SQL Server database on PostgreSQL. Everything is ok except I can’t find how to recreate this index: USE [mytablename] GO CREATE NONCLUSTERED INDEX [myindex] ON [dbo].[mytablename] ([col1],[col2]) INCLUDE ([col3],[col4]) GO Will be very grateful for help. Alexey Update: http://img38.imageshack.us/img38/1071/89013974.png here is db structure star+eav there is only one query SELECT this_.id […]

Should User and Address be in separate tables?

Currently my users table has the below fields Username Password Name Surname City Address Country Region TelNo MobNo Email MembershipExpiry NoOfMembers DOB Gender Blocked UserAttempts BlockTime Disabled I’m not sure if I should put the address fields in another table. I have heard that I will be breaking 3NF if I don’t although I can’t […]

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