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 […]
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 […]
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 […]
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 […]
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 […]
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 ?
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, […]
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 […]
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?
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!