Tag: database-design

Using Multi Column Unique Indexes vs Single Hashed Column

I’ve a table which I need to give unique constraint to multiple columns. But instead of creating multi column unique index, I can also introduce an extra column based on hashing of all the required fields. So which one will be more effective in terms of database performance? MySQL suggests the hashed column method but […]

How do I make a comment system that deletes all descendants of a comment when the comment is deleted?

I’m making a full-stack web application and am trying to write as much logic in the database as possible. I have a comment system like 1 / \ 2 3 / \ 4 5 that I’m representing like Comments =============== id | … ————— 1 | … 2 | … 3 | … 4 | […]

Is having a single column table in SQL Server considered a bad practice?

A coworker and myself stumbled upon a newly acquired database schema with multiple tables that seem to only have one column. One table seems to be some sort of type, and the another for some sort of frequency, etc. We are only dealing with a schema here so there’s no actual data to go by. […]

Representation of repetitive dates in SQL database

In my program I must manage some holidays. Each holiday can be an exact date, or a repetitive date. For example: -6th January of each years -first day of each month of each year How would you I save this information on db? I can’t use Datetime because with datetime I can’t represent the particular […]

How to set up an extra constraint for 1-to-many relationship without using triggers

I have come across a database in my current position that needs allot of TLC to say the least, there are many cross reference tables all having the same structure schema wise, and all using triggers to enforce business logic, or domain integrity it would seem. I am trying to figure out if I can […]

Database schema for titles and partner titles

I have a standard Title table having below structure: TitleId Title —————————– 7282991 CEO 7282992 CTO 7282993 SSE 7282994 SE 7282995 CFO Now we have a requirements that users can add their own custom titles along with these standard titles. So I created a table like below. TitleId Title UserId ———————————— 7282991 Manager 1 7282992 […]

How to avoid one column table in database design

So, what would be the best approach to design relationship in a table when I have this setup: Let’s say I have tblFile that holds file path’s to different files. and then I have bundles that can contain any amount of files. So, my current structure is as follows: How do I avoid creating tblBundle […]

Primary key in table

I have table USERLIST with [UserName],[AppId] and data will be like UserName AppId ——– —– abc 1 abc 18 abc 2 abc 24 abc 29 abc 30 abc 33 abc 4 abc 5 abc 6 abc 8 xyz 1 xyz 18 xyz 2 My query will be mostly on USERNAME like select applist.APPID ,applist.APPDESC ,applist.APPTHUMB […]

How to design database tables for hierarchical data with unknown depth?

I want to create hierarchical data with unknown depth like this: Create categories and sub categories and for those subcategories they will have also subcategories and so on. The depth of the subcategories is unknown and only will be done in the runtime by the user. What I though about is to but them all […]

Surrogate vs. natural keys specific example

After lots of reading, I decided to make surrogate keys for my tables. Now I have a dilemma on creating a 1:1 relationship on two tables, “Orders” and “Form_VSA_albums”. 1:1 because one form can only have one order and one order can only have one form. Note, there CAN be orders with NO form but […]

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