Tag: database-design

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 […]

Many-To-Many Recursive Relationship Without a Primary Entity

I have had multiple needs for a linking table for recursive relationships where one record is not a clear “parent” record over the other. Here is an example that I hope makes my issue as visceral as I can display or explain it: (Person) Id Person ————- 1 Bob 2 Sue If Sue was Bob’s […]

Changing Opening Hours without affecting historic data

I’ve been tasked to create a data visualisation dashboard that relies on me drilling into the existing database. One report is ‘revenue per available covers’ – part of the calculation determining how many hours were booked against how many hours were available. The problem is the ‘hours available’, currently this is stored in a schedule […]

SQL – define keys to table

Is there any considerations to define keys for table that has lot of records already and most of operation that are operated on it are Insert ?

Database design to support multiple clients

If you were building a database with tables Documents, Clients, Users and Firms, where in Firms you would store the companies that use the software how would you design the first three tables to support multiple Firms to store in them? So, in Documents we want to store documents for all the firms, of course, […]

MS SQL: One Large, Multi-Column dbo.Person table or Multiple Subsets With Multiple Joins?

I am thinking through the layout of my “Person” table in my MSSQL DB. Is it better to have all columns in dbo.Person as such: Person ( personid first last email confirmcode isconfirmed homeaddress homecity homestate homezip session ipaddress workaddress workcity workstate workzip etc… ) OR, is it better to partition the table into related […]

how to enforce multiple unique fields in database table

i have a table called Cars and the primary key of the table is ‘id’. I also have a field called ‘name’. I would like to make sure no one enters the same name twice even though it wont break my db integrity. what is the best way of doing this?

Visio data model diagram to db

I have created my db data model diagram using Visio. Is there a way to generate the DB schema from the Visio? Appreciate your response!

Grouping records with or without a foreign key constraint

I have a table containing objects, something like this: PK ObjectId FK ObjectTypeId Description etc The objects need to be grouped. I have been given a number of suggestions, all of which ‘work’ and some I like more than others. None of them are perfect so I’m struggling to settle on any particular model. 1/ […]

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