Best Practice For Database Design Scenario
Sep 26, 2007
Hi,
I'm currently building a database that is going to have at least 6 different types of users accessing it via a web application.
A user will have different information collected about them. A few of of the users will have the same or similiar information collected about them.
I will be using role based authentication.
Now my query is this;
Scenario 1:
Would it be a good idea to keep all the users common information in one table. Ie. thier username,email,password,name. Then create a tblDetails for each type of user that would contain the different data. That way i can just check one table to verify thier login credentials.
OR
Scenario 2:
Would it be best to create a seperate table for each type of user and then log them in based on the credentials stored in each type of users respective table.
Hope this is clear. I'm leaning towards scenario 1, although I've used scenario 2 before. Just wondering which would be preferred.
Cheers
RobC
View 2 Replies
ADVERTISEMENT
Oct 10, 2005
Hello All ..
This is the scenario I'm having :
-- I'm a beginner so bear the way I'm putting it ... sorry !
* I have a database with tables
- company: CompanyID, CompanyName
- Person: PersonID, PersonName, CompanyID (fk)
- Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)
In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...
There could be different ways to do it like:
1) - In the supplier stored procedure I can read the ID (SELECT) and :
if it exists (I save the existing SupplierID - to 'return' it at the end).
if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end)
------------------------------------
2) - Other way is by doing multiple stored procedures,
. one SP that checks,
. another SP that do inserts
. and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)
3) it could be done (maybe) using Functions in SQL SERVER...
There should be some reasons why I need to go for one of the methods or another method !
I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!
I'll appreciate your help ...
Thanks in Advance . ! .
View 1 Replies
View Related
May 7, 2007
Hallo I need some hints because I would like to set up my service broker in the proper way.
First: I'm going to setup service Broker between 2 databases on the same sql server instance.
My Goal: I insert the data on table1 in the DB1.On table1 there is a trigger that begin conversation and send the message to the service on the DB2.
On the receiving queue there is an Activation that take the xml message, shred it and save the content on th table 2 on the DB2.
Actually this SP is my main concern because the shred and insert involves also several checks so it could take "a while".
Volume of data: it seems that there are 100-200 daily insert in the table1 on the DB1 and it is possible that some of them arrives at the "same time".
Very quickly:
On the DB1 the trigger:
Notice that I reuse always the same conversation and the send queue as RETENTION = OFF
begin transaction;
begin
set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceiveService');
if @dialog_handle is null
BEGIN
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [SendService]
TO SERVICE 'ReceiveService'
ON CONTRACT [MainContract]
WITH ENCRYPTION = OFF;
END
SEND ON CONVERSATION @dialog_handle
MESSAGE TYPE Message ('uyiuy')
commit;
END
On the DB2
ReceiveQueue is defined as:
CREATE QUEUE [dbo].[ReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceivedTrade] , MAX_QUEUE_READERS = 100 , EXECUTE AS N'dbo' ) ON [PRIMARY]
But most important is the Activation's SP and this is the main code:
while (1 = 1)
begin
begin transaction
-- Receive the next available message from the queue
WAITFOR (
RECEIVE top(1)
@message_type=message_type_id,
@message_body=message_body,
@dialog = conversation_handle
FROM ReceivedQueue
)
if (@@ROWCOUNT = 0)
BEGIN
Rollback Transaction
BREAK
END
SET @ErrorSave = @@ERROR ;
IF (@ErrorSave <> 0)
BEGIN
ROLLBACK TRANSACTION ;
SET @ErrorDesc = N'An error has occurred.' ;
END CONVERSATION @dialog
WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;
INSERT INTO [dbo].[tblLog] VALUES(@ErrorDesc,NULL)
END
ELSE
BEGIN
'this is the SP that does most of the work
exec [dbo].[sp_ShredXMLMessageToRelationalData] @message_body
END
commit transaction
I have to say that everything works fine but I don't think that it is completly performant.
I read several blogs/forums and now I'm confused on the best way to implement service broker messaging:
These are my questions:
1) I read that is the DB's are in the same instance It can improve performances the send the message directly on the Queue2.
Can be useful in my case? How to implement it?
2)In my case it is necessarly that I send a message back to the initiator?
3) In my case the conversation never end. I don't think that this is correct but in case of the data push who has to END the conversation? The target? the initiator? never end to avoid overhead (I read that big overhead is caused by the BEGIN and END DIALOG)?
3) where (initiator or Target) and how to handle the service broker error? In my case my applications are SPs and I need to inform the developer or DBA that something went wrong during the processing ( conversation or shredding stored procedure).
4) In my case is should be a good idea to think about how to solve the possibility of the "poison messages"
for sure I will find out other questions...
Any hint, link is appreciated!
Thank you very much!
Marina B.
View 17 Replies
View Related
Dec 11, 2004
I'm building a database that has maybe four unique tables Student,
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.
View 11 Replies
View Related
Nov 1, 2007
I have a SQL 2000 server that is installed on a Dual Xeon server running win2k. The server has two raid 5 hard drives, a C drive and an E drive.
The C drive is currently where the operating system files are stored as well as the SQL program files. As things stand there are SQL DB and transaction logs strewn between these two drives with no particular logic.
My question is, with two drives as it stands how should I move things around to gain the best performance? For example, should I keep all my data on the E drives and all my transaction logs on the C drives with the OS and the program files?
There are about 10 Databases in use. One database run's the configuration for proprietary predictive dialing software. The other databases are calling information for each campaign we run within the dialing software.
I have enough space on both drives to accommodate the data, its performance I would like to see a difference in.
View 6 Replies
View Related
Apr 2, 2008
Hello,
I am new to SSIS. I need to transfer data from SQL Server 2005 Operational Database to SQL Server 2005 Report Database. The upload needs to work every night. There are few master tables and remaining are transactions tables.
I am planning to create 2 packages one for master tables and other for transactions tables.
Is it the good approach?
Also few of transaction tables are heavy in terms of number of records. Will it better if i further break them in many packages?
I am using book "Microsoft SQL Server 2005 Integration Services Step by Step".
Can you suggest any other good book?
Regards,
Shailendra Shelar
View 7 Replies
View Related
Apr 12, 2008
Hi all,
I have a problem...
I need a database file for a bank with info like customers, transactions and other bank stuff etc. It is for a university assignment. basically I just need to analyse, mine the data etc the info in the database
I have looked on msdn but none of they're databases are for a banking scenario.
Does anyone know of a sample bank database anywhere or does someone have one they could give to me ?
Thanks for your help in Advance
Peter
View 2 Replies
View Related
Jan 24, 2007
Good Morning,
I work for a company that has sees alot of people come and go. The one thing I have noticed is that people use their admin accounts to log into SQL and create sp, views and databases.When the user leaves I am stuck with all these objects that are owned by somone no longer working for the company.
So my question to you guys is: What is the best practice to use in creating new objects?
Thanks for your guru-ness!
View 3 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
Nov 17, 2006
Can you tell me what is the best practice for SQL database migration fromone DB server, to another one, new DB server. The old DB server will beremoved.1. Backup from old and restore all databases on the new server2. Export data and copy/import on a new server3. Something else...Thanks in advance for any good advice...Regards
View 2 Replies
View Related
Mar 5, 2008
hi experts,
i have a postcode database that i need to update. the database cnotains of 6tables, the file i ahev has all the information at once, so i have to organize it and insert records into the appropriate tables.
this is the first time i'm doign this so i would like to know what the best way to do? do i need to create a stored procedure or a script, or may be something special and efficient that i do not know yet.
any advise will be very appreciated
thanks in advance
View 1 Replies
View Related
Mar 13, 2006
Hi,
I am new to working with Database using .net C#. i will really appreciate if someone can point me to a link where i can find best practices of working with sql express database with my c# web service applications.
preferably i would like to have answers for:
- to have one db or to have one for testing and one real db?
- db security
- use windows authentication or db user authentication
etc.
View 1 Replies
View Related
Sep 11, 2007
I'm not sure this is the exact forum in which to ask this question, so if there is a more appropriate forum, please point me in that direction.
Basically I'm looking for advice and best practices for dealing with an upgrade plan for a hosted database. Here is the situation:
- Hosted software application (.Net)
- Uses SQL Server 2005
- When we upgrade the software application to a new version, there will be schema and data changes that need to be applied to the database
- For the software application, we have considered upgrading an offline machine and then switching old one instantaneously.
- For the database, I'm trying to figure out if we can keep everything live for upgrades, or if we should have a small amount of scheduled downtime to upgrade.
So we have identified 2 basic directions:
1) Keep the database live and apply the new script changes just after the application software is upgraded. This means zero downtime, but we'd have to design and test our application to be backward compatible, so that the new version of the application code could handle both the new schema design, and the old schema design. It seems that this option would add a huge amount of complexity because we would essentially be testing a 3rd app/schema combination.
2) Take a small amount of scheduled downtime and upgrade the application code and database all at once.
Obviously option #2 is the most desirable from a development process point of view, and #1 is the most desirable option from a business point of view (zero downtime).
So, I'm looking for some advice on this. Do most people take the whole system down? Are there 100% live systems that deal with this? Are there other approaches I'm not considering?
Any information would be greatly appreciated. Thanks.
View 4 Replies
View Related
Jul 27, 2007
With regards to time zones, daylight savings, and web users, is there a best practice for storing date & time information in a database?
For example, my databases are hosted in Time Zone A, but the web users are in Time Zone B. Then, when I create a rss feed (which is displayed in GMT), I add a third time zone into the mix for the same data. To date (no pun intended), I have been entering the date/time data in the time zone of the database server (Time Zone A), and then converting it using an application setting in the web.config file (i.e. TimeZoneBOffset = -1, GMTOffSet = -5). In other words, each time I display a date I calculate what it should be using the time-zone offset in the web.config. This also enables me to account for changes in day light savings, etc.
My concerns are three fold: 1. What if I move the database to another server and the time zone changes? 2. Right now the users are in only 1 time zone. If I expand it to several then the offset will have to be by users, which is do-able, but something I haven't had experience with in the past. 3. It is likely more efficient to calculate the time zone once on input into the DB, rather than in each use like I'm doing now. What time zone baseline for insert into the db should I use?
Thanks in advance for your help!
PS My application is primarily looking at 'smalldatetime' data - down to the 'minute' level.
View 6 Replies
View Related
Feb 7, 2006
What's the best practice for adding / editing a record into a database with lots of fields ?I am not talking about the mechanics of it, as there are a lot of trivial examples using ADO.NET, stored procs, etc.
Deleting is easy, you just pass in (a few) primary key/keys to uniquely identify the record.
But in the real world when you have, say, a table with 100 fields! Do you code the INSERT sproc by hand, with 100 parameters... then call it with your ADO.NET code ? sounds like a lot of work to me...
What about updating! That's even worst, sometimes you may need to update only 3 or 4 fields, but using sprocs you would have to pass the whole 100 parameters in again, and "update" the whole record (when in fact you are only changing 3 or 4 fields).
With the update i could write different sprocs targeting only the fields i wish to update, but that sounds like duplicating work, vs having one generic update proc.
Sometimes i just feel like bypassing sprocs and having inline sql as it would be less work... but i know it is untidy.. and more potential to be buggy.
So come on guys (and gals)... let's hear your thoughts on how you would handle the insert / update scenarios when you have lots of fields ? Northwind examples are too trivial :-)
View 1 Replies
View Related
Feb 13, 2002
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View 2 Replies
View Related
Oct 29, 2015
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jul 23, 2005
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies
View Related
Aug 16, 2007
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies
View Related
Sep 27, 2007
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
View 14 Replies
View Related
Apr 12, 2007
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
View 7 Replies
View Related
Oct 20, 2006
Hello, I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..andwhat sort of service or products they can supply. For instance a Employment agency maybe under professional services Table 1 Customer Customer_ID = primary key,,,, Sector_ID = Foreign keyComapany Name, Address, Phone, Postcode etcTabel 2 Industry SectorsSector_ID = primary key,,,,Customer_ID= foreign key banking, Education,Prof Services, etc Table 3 Trading ActivityTrading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= FkEmployment Agent, School, Lawyer etcTable 4 ProductsProducts_ID = primary key,,,,Trading_ID = foreign keySupply frozen foods, transport services, sports goods, etc Table 5 Account Account_ID = primary key,,,,Customer_ID = foreign keyAccount Name, Credit Limit, Payment Terms, Open date, Account contact etc One big point of confusion is, can I have the Customer_ID from the principal Customers tablein every table as a foreign key or must the tables be chained together one after the other as such. Advice appreciatedThanks
View 1 Replies
View Related
Apr 10, 2007
Hi,
I need a hand with designing a database.
I am collecting results from a survey which has the following questions:
Call ref? How did you place your support call? Were you satisfied with the amount of time you had to wait until getting acknowledgement of the support call placed? 1 = very satisfied and 10 = very unsatisfied. How happy were you with the customer service you received upon placing the support call? 1 = very unhappy and 10 = very happy.How satisfied were you with the amount of time you had to wait until you heard from an engineer? 1 = very satisfied and 10 = very unsatisfied. How satisfied were you with the time taken to get your problem/query resolved? 1 = very satisfied and 10 = very unsatisfied Did you feel the engineer had enough knowledge to deal with your call? 1 = very good and 10 = not very good Overall how satisfied were you with the support call placed? 1 = very satisfied and 10 = very unsatisfiedIs there anything we can do to improve the quality of the support and service you received?
I want to store this in a database. Obviously I want to use best practice for design, normalisation etc. The stumbling block I am coming accross is the fact that each question has a number and each question has a score from 1 to 10 and storing this in the database. Any help appreciated!
Thanks
Andrew
View 9 Replies
View Related
Apr 26, 2007
I am creating database tables for company testimonials. Database columns: name, position, companyname, comment, service we provided.
My question is that for each company - may have a multitude of different services from us, and different people with different positions in the same company may make comments.
What is best practice for putting this db structure together?
Thanks
Andrew
View 1 Replies
View Related
Oct 29, 2007
Can anyone tell me how can i design database architecture for the Table Category & Product...so that i can make N-level entity relation....I have database in SQL SERVER 2000.
View 2 Replies
View Related
Jan 30, 2008
I guess I am confused about something and need some help. I am looking at a database schema for about 20 tables in a database. I noticed that the firstcolumn in each table Is some type of Id. For example StudentId,TestId etc.Where the Id is a unique numeric sequential value.
So I have some questions?1- Do these Id's act as what are called indexes for the table?(Unique indexes)2- If the answer to 1 is correct, then how do I create these unique indexes? Is it as simple as declaring the Id column as the primary key and that this value will be generated automatically upon insert? 3- Is it necessary to have an Id column for every table, or may I only do it for a few of them?4. In relation to Question2, what do I need to do, so that the Id column will automatically be created when someone inserts a value into the database table with an ID column.
For example here are two sample tables
StudentsInfo
StudentId<PK> Name Age 1 Mark 33 2 Jill 23 3 Mary 25
PersonalInfo
Name<FK> SocialSecurity Address MajorMark 324-444-3342 15 Elm ArtJill 888-888-8998 21 North ScienceMary 876-777-2344 18 Byle Music
View 6 Replies
View Related
Apr 17, 2008
Hi,I am planning to create a technical forum for our college. could any one give me an idea of how to design the database for the forum. what table and columns i should have?
View 4 Replies
View Related
Nov 3, 2004
I have got a design issue.I have got 4 tables,having relationships.Now,user will edit records and save them,but I want to save the values which were exixsting before they were edited and saved.Implying,if a record is edited 50 times,then the values of each edit needs to be saved.How best ,can I achieve this.
View 3 Replies
View Related
Oct 20, 2005
Hi !!We are designing a system where we ask people for their interests and store in into the database and send customize email. Following are the questions:1) Should we use Identity column as Primary Key and CustomerID column? OR we should create Custom CustomerID and use it as Primary Key? (I have read few articles about Identity column as Primary or not Primary, but need little advice what to accept)2) We have a Tables called : Interest & Customer_InterestCustomer Table:CustomerID, Customer Name, Address, Email, Signup DateInterest Table:InterestID, InterestNameCustomer_Interest: (Need suggestion for How to design this)Should Table be design like:Option1: CustomerID, InterestIDOption2: CustomerID, Interest1, Interest2, Interest3, Interest4i.e.Lets Say: Customer table has CustomerA, CustomerB, CustomerCInterest table has Interest I1, I2, I3, I4Lets Say CustomerA Signedup for Interest I1, I2, I3 and CustomerB signed up I1, I4As per Option1:Customer_Interest Table witll haveCustomerA, I1CustomerA, I2CustomerA, I3CustomerB, I1CustomerB, I4ORAs per Option2Customer_Interest (Where Interest Column is bit column..... 1 = Signed up, 0 = Not Signed upCustomerA, 1, 1, 1, 0CustomerB, 1, 0, 0, 1Which way we should design? 3) If we select Option2, and if we are displaying data in ASP.NET Page, will there be any issue if we use 3 tier architecture?Thanks !!!
View 18 Replies
View Related
Nov 10, 2005
I am designing an inventory database in which I need some help, I have the following entities:ItemsNotebookWireless CardADSL Modemetc...ModelsAcer centrino 1.6Acer centrino 1.733COM 4x125 hours Wi-fi access50 hours Wi-fi accessetc... PackagesPackage A:Acer centrino 1.63COM 4x125 hours Wi-fi accessPackage B:Acer centrino 1.733COM 4x150 hours Wi-fi accessI made a table for the items having the following fields: Item_ID, Item_Nameand another for the Models having: Model_ID, Model_Name, Item_Type(Foreign Key to Items table)up to this point is this correct?About the packages table, I don't know if it is correct to have a field for each model (one for notebook, other for modem, and other for wireless card) like this it would be like having 3 foreign keys to the same table but nothing distinguishes themI don't know how to relate the packages and the models table.Any recommendations for a proper design for those entities?
View 6 Replies
View Related
Dec 20, 2005
I am creating a knowledge base. I want the user to be able to choose a category such as Hardware, Software, Etc. then I want them to be able to choose what type of software such as word, excel, ets.
It will then display in a gridview problems, solutions, submitted by, last updated, and review date.
The gridview will allow users to update solutions and I would also like the ability for technicians to be able to add new Problems/solutions.
What would be the best way to handle this?
Any tips are appreciated.
Rick
View 3 Replies
View Related
May 2, 2000
I have requirement to create table of 100 columns. Should i create 100 columns in one table or i should create three coulmn table with column id, column desc, column value with 1 row in 100 column table = 100 row in 3 column table. Which will be faster & efficient in performance, io & size.
Thanks
Manoj
View 1 Replies
View Related
