Tag: database-design

a special case when modifing the database

sometimes i face the following case in my database design,, i wanna to know what is the best practice to handle this case::: for example i have a specific table and after a while ,, when the database in operation and some real data are already entered.. i need to add some required fields (that […]

What are some methods to write scalable services when using a SQL Server 2000 table as a queue of work?

If I have a SQL server 2000 database table I’m using as a queue of work to be performed, what are the best methods for doing that work in a scalable way? The scenario: There is a windows service which is going to perform some, potentially long running, work for each row in a table. […]

FacebookId as primary key?

Will the facebookId ever change? If not, Is it safe to use it in my app as the primary key? FACEBOOK_USER FacebookId (PK) Name FACEBOOK_FRIEND Id OwnerFacebookId (FK) FriendFacebookId (FK)

BuyerAccounts and SellerAccounts Tables that both reference Accounts Table. Create Account INSERT

I am designing tables to store accounts of website users. There will be two types of accounts for the website, BuyerAccounts and SellerAccounts. My approach is to have a master Accounts table that will store information that is common to both types of accounts and then to have a table each for BuyerAccounts and SellerAccounts. […]

Stored procedure vs. DTS/SSIS for data inserts in SQL Server

Which way of inserting large amounts of data into a table is more efficient and/or considered best practice – using INSERT INTO statement in the stored procedure, or using DTS/SSIS data transformation taks? The source data lives on the same SQL server as destination table.

Representing dates which are a certain number of days before or after another date in a RDBMS

Is there a standard way to represent dates which are a certain number of days before or after another date in an RDBMS? For example, let’s say Date 1 is 30th October, 2005, which can obviously be stored in a datetime column or similar in the RDB. But let’s say Date 2 is “3 days […]

Database design to access/manage multi-organisation

How would you design a database to manage multi organisation? (ie 1 user can own/manage more than 1 organisation) Example Xero (www.xero.com), you can login to xero then select the company on the list that you want to manage. I think freshbook has something simular. USER_ACCESS Id CompanyUserId (UserId of company) UserId (UserId that will […]

Social Network Database Design – Friend/Block Relationships

I’m working on a social networking site and need users to be able to friend each other and/or block each other. The way I see it, 2 users can either be Friend, Pending, Block, or NULL. I’d like to have a single view that shows a single row for each confirmed relationship. My view properly […]

How to Design “Country”, “Province” and “City” Tables?

EXAMPLE 1: xTable: xTableID, CountryName, PovinceName, CityName OR EXAMPLE 2: Country: CountryID, CountryName Province: ProvinceID, ProvinceName City: CityID, CityName Question: Do you recommend me to populate country, province and city lists at application level and then use EXAMPLE 1 design? or Should i populate it at DB level as in EXAMPLE 2? City will show […]

Composite foreign key in SQL Server

I have a table named Books which contains 3 columns. TableName: Books Columns: BookId (PK), BookName, Book_Publisher_XRef_Id (FK), IsInternal I have two tables that contains publisher information. Both these tables have different set of columns. TableName: InternalPublishers Columns: PublisherId (PK), PublisherName, …. TableName: ExternalPublishers Columns: PublisherId (PK), PublisherName, …. I have a link table that […]

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