Optimal Database Design
Mar 2, 2005
What is more efficient for a database design - a lot of tables with only a few records or a few tables with lots of records.
I'm starting a new site and each user will have numerous records but I'm not sure whether to have a few very large tables (over 100,000 rows) or start a new table for each user which would result in approx 1500 tables most of which would be the same table design with different rows.
I'm using SQL2000.
I guess this is quite a basic question, but I'm a bit unsure.
Any references anyone could point me too as well.
Thx
View 3 Replies
ADVERTISEMENT
Jan 3, 2008
C# .Net Application as front end
Sql Server2000 as back end
I need to merge an external dataset from .Net app(in XML format) with the information in database with one column in database table as the merging criteria. A situation similar to Left Outer Join, wherein i need all records from external dataset and if matched in database the corresponding values from there too, the only difference here is that the join is not between two Tables its between a table and external dataset.
There is no need to store the external dataset in the database in persistent form, its just a query - merge - response operation.
So, can anyone suggest the best possible solution for this? A table variable / temporary table / some other schema, what and how?
Thanks in advance..
View 8 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
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
Jul 23, 2005
Please excuse what is probably a no-brainer, but here goes.Is there any difference, in terms of performance or any other pertinentfactor, between:SELECT * FROM tblCustomers INNER JOIN tblCustomerOrders ONtblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerIDandSELECT * FROM tblCustomers, tblCustomerOrders WHEREtblCustomers.fldCustomerID = tblCustomerOrders.fldCustomerIDI note that if I type the latter into the SQL pane in a Data window,SQL Server replaces it with the former.TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk
View 3 Replies
View Related
Feb 3, 2004
Hi
I'm fairly new to this, so bare with me...
I have to make a new installation of an MS SQL 2000 EE on a Windows 2003 Std. Edt.
HW:
---------------------
Dual Xeon 2,4 + 1 GB Ecc
1 x 32 MB Adaptec 2100S RAID Controller
2 x 18 GB 10K HD
4 x 18 GB 15K HD
---------------------
So far I have made following configuration....
---------------------
2 x 18 GB 10K HD / RAID 1
- C:OS
- D:MSSQL program files + System DB's (Master, pubs ect.)
4 x 18 GB 15K HD / RAID 5
- E:TempDB
- F:Data + Logs
---------------------
But I'm not sure that this is the optimal configuration, and I'm willing to start all over :)
So my q's are.......
--------------------
Which RAID configuration would you suggest?
Which partitions on the raids would you suggest?
Which usage would you assign the various partitions?
How do I move the system and temp db's?
--------------------
Thanx!
Regards,
Taras Bredel dk
View 3 Replies
View Related
Jul 25, 2007
Hi
I'm trying to find the optimal way of getting the timestamp of the last updated entry in an mssql database. A database is updated only about 5 times a minute, how ever a request for the time of the last entry could be around 1 per second. For this reason i was thinking of having a separate table which has a single row which is updated everytime a new entry is updated in the main table. I would then only need a simple SELECT statement and need very little processing power.
Is this the best method, or can you think of any others i could use?
many thanks
View 14 Replies
View Related
Jun 19, 2008
I am wondering if 100% buffer cache hit ratio is considered not good in general?
Are there instances that it is actually bad and can contribute to server performance degradation?
Any thoughts on the topic most welcome :)
--------------------
keeping it simple...
View 11 Replies
View Related
Jul 23, 2005
I am working with a report generator that is based on SQL Server 2000 anduses ASP as the UI. Basically we have a set of reports that end users canexecute through a web browser. In general the model works fine, but we arerunning into some scaling issues.What I'm trying to determine is, what is the optimal configuration for thissystem. It is currently a 2.4G Pentium with a large RAID and 1G of RAM. Wehave been using the "fixed" memory configuration, allocating 864M to SQL.This is on a Windows 2003 server box.This works fine when a "small" query or two is executed, but the performancesuffers terribly when several users try to run reports in parallel. A singlequery might take 10 minutes to run if nothing else is happening on the box,but if additional users log on an run reports, it's almost impossible topredict when the queries will finish.I am also looking at the effect of database size on performance, runningtests against a database with 1 month, 3 months, and say 12 months of data,running the same query against 2 databases in parallel. With the originalconfiguration, the results were all over the place, with the 12 monthdatabase outperforming the smaller dbs, while other times there was littledifference. It seems that once the system starts paging, and paging heavily,it's over; the system never "recovers" and queries that previously ran in afew minutes now take hours.I added 3 G more memory to the system, and modified boot.ini to include the/3GB switch. Now when I run the same tests, the results are much moreconsistent, as the system rarely ever has to swap. Then again I've neverseen it go past 1.7G in Task manager, making me think that any more than say2.5G of memory is a waste?Things we are trying to determine are:- in the SQL Server memory configuration, is Fixed better than Dynamic? Wehave read that Dynamic is not good at returning memory to the OS once it'sbeen allocated- What else can we do to optimize the performance for this application? Itseems to me if the indexes are properly designed, the database sizeshouldn't have that much impact on performance, but this appears to be trueonly to a point. In comparing the execution plans between say a 12 month anda 3 month database, the plans are sometimes dramatically different. I assumethis is due to the optimizer deciding that going directly to the base tablesand not using an index will result in better performance, when in reality,this doesn't always appear to be true.- Are there other SQL Server switches I should be tweaking? Is there somenumber of simultaneous queries that this configuration should be limited to?- What about other versions of SQL Server (e.g. Enterprise, Data Center,etc) would these buy us anything?Thanks for any advice,-Gary
View 2 Replies
View Related
Nov 23, 2005
I have the following tableCREATE TABLE Readings(ReadingTime DATETIME NOT NULL DEFAULT(GETDATE()) PRIMARY KEY,Reading int NOT NULL)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050101', 1)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050201', 12)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050301', 15)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050401', 31)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20050801', 51)INSERT INTO Readings (ReadingTime, Reading) VALUES ('20051101', 106)GO-- list the tableSELECT ReadingTime, Reading FROM ReadingsGOIt is a table of readings of a free-running counter that istime-stamped. I need to determine the value of the reading thatcorresponds to the closest date to the supplied dateAre there more optimal/efficient ways of accomplishing this than thefollowing?DECLARE @when DATETIMESET @when = '20050505'SELECT TOP 1 ReadingTime, Reading FROM ReadingsORDER BY abs(DATEDIFF(minute, ReadingTime, @when))The above gives me the desired result of ('20050401', 31).Any suggestions would be appreciated
View 1 Replies
View Related
May 14, 2007
I would like to know what options are available from BIOS / OS / SQL and server perspective when configuring or tuning a system with SQL Server 2000 or SQL Server 2005.
For example, I have a system with 4 dual-core Opteron CPUs on Windows 2003 Enterprise Edition. However, the OS sees 8 CPUs -- is this the optimal configuration or is it better (if even possible) to configure the system to see only 4 CPUs? The reason for this concern is due to performance problems faced deploying systems with Hyperthreading Technology.
Any documentation or examples in this regard would be very useful. Basically, what are the scenarios that would require a certain type of CPU configuration over another.
Thanks in advance for your help,
Ziggy
View 1 Replies
View Related
Aug 24, 2007
What is the best performance for this configuration:
Files:
Data
Log
Indexes
tempdb
Disk:
A - RAID 10
B - RAID 10 (or should this be RAID 1?)
Whats best?:
A - Data and Indexes
B - tempdb and Log
??? Thanks.
View 1 Replies
View Related
Aug 5, 2014
I have a VM set up for offloading DBCC checks. Specs are below. I've read through this, but I'm not seeing the performance gains by enabling the trace flags and using the physical only switch.
Is the whole drawback that I'm on SATA storage? Is there a VM configuration with the CPU I can/should change? I've been playing with MAXDOP trying to see if I can get any benefits but I'm not seeing a much.
wait_type wait_time_spctrunning_pct
CXPACKET 561191.4228.7128.71
OLEDB 387136.7619.8148.52
PAGEIOLATCH_SH 340674.5817.4365.95
TRACEWRITE 321598.8416.4682.41
[code].....
View 9 Replies
View Related
May 28, 2014
I would like to build a report with nice functionalities like filter, sorting, drill-down, something like a PowerPivot Table, but with some layout/design/format capabilities. I would also want to publish the report, refresh it let´s say once a week, notify users when a new version is available, etc.
If I use PowerPivot, then I am not able to customized the report or to mix data from different sources in one table.
If I convert the cells of the PowerPivot table to workbook formulas I lose the filter, sorting, etc functionalities.
I still have to try using Reporting Services, but I think that always something is missing.
View 1 Replies
View Related
Dec 10, 2006
Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks
View 9 Replies
View Related
Feb 20, 2004
I was told in one of my systems classes that the real performance bottleneck in accessing information from the database was the opening of a connection from the application to the database.
To combat that problem I was advised to use a Singleton Factory pattern and to have that Factory instaniate a connection and open it, then pass references to that connection for all of the objects that it created. All of those objects passed the connection reference to the objects they created and so on. Basically that meant that I only ever had one connection open at any one time for my entire application. And I was able to implement this solution at my previous job where I was developing in Oracle. I primarially used OracleCommands and OracleDataReaders to get the informaton into and out of the database. I thought this was a very nice solution. Having this many DataReaders accessing a single connection was not a problem because OracleConnections don't get locked from having more than one DataReader open at once.
At my current job, however, I use SQL Server. I am concerned that the single connection will not work in my new enviroment as the SQLDataReaders lock up the connection while they are using it. If the information that I recieved about opening connections being the real bottleneck, then I am hesitant to have a connection instanciated and opened for each method, but I am concerned that a whole lot of errors will be generated if I use the single connection method. Also, how do DataAdapters effect my decision of which approach to use.
Any advice would be most helpful. If you have any questions that would help answer just ask. Thanks.
View 4 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
