I have to come up with a design choice for a DB Table for a financial firm(i.e. Tables with Fixed column or Value-Pair).
Case: SQL Server 2005 is used as a back-end for a web application(which basically is designed for doing web based analysis). The business people are insisting on a feature that enable power users to add fields to the underlying tables. One option proposed is named value-pair design approach? But from performance point of view, this seems a nightmare. How can this be integrated into static-column table approach. FYI, security policy of the client do not allow the underlying table structures to be changed by the user and currently
there are 5000 fields, with a possibility to grow.
I have a question regarding table design (I'm using SQL Server Express 2005). Is it better and is there a performance gain by using layout # 2 instead of layout # 1? Someone told me it was better to divide everything up but it seems kind of useless here, plus it complicates the handling. So I want to know which one is faster (or better) considering large amount of records. Thanks! Layout # 1Objet==========ObjetIDClientIDObjetTypeStateObjetNoTitleDescriptionStartDateReturnDateEndDateLayout # 2 Objet==========ObjetIDClientIDObjetTypeStateDetailsObjet==========DetailIDObjetIDObjetNoTitleDescriptionStartDateReturnDateEndDate
Im trying to design a database to use in SQL server , the design i've got now is working but It just doesn't look right to me and there must be a better way to set things out.
Heres the current situation
Theres 3 tables
USERTYPES
USERS
TICKETS
The users table holds the user information i.e.
userID userName Phone Number userTypeID *
It contains a foreign key called userTypeID which is a primary key of USERTYPES
The USERTYPES table contains the two types of users on the system , these are standard and admin, so the table looks like this.
USERTYPESID typeName
The TICKETS table holds the ticket details of tickets submitted by users. so
Now here is where I have the problem, the ticketRequestor is a foreign key of the STAFF table staffID however AssignedTo is also a foreign key of the STAFF table staffID.
Currently in order to get this working i've had to turn off the enforce relationship for the AssignedTO - staffID link but idealy I think this enforce relationship should be on to ensure its a truely relational.
any ideas as to what I need to do to organise the relationships better ?
In the past, we had table names such as: PurchaseOrders SalesOrders
Now with SQL 2005, you can use schema namespaces, so it could become: Purchasing.Orders Sales.Orders
So essentially you can use exactly the SAME table names, in different schemas.
So... would you recommend to change to the last model, or just stay with the old way? What is best practice?
One of the reasons why I ask this question is that I looked at the AdventureWorks sample database included with SQL Server 2005, and Microsoft is using:
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2 e.g Sales tax = 10% Use Tax = 10% Share1 = 60% Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on.. and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size. Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 3: 1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or 2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier. 3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
I have a table (Sql server 2000) which has 14 cost columns for each record, and now due to a new requirement, I have 2 taxes which needs to be applied on two more fields called Share1 and share 2 e.g Sales tax = 10% Use Tax = 10% Share1 = 60% Share2 = 40%
So Sales tax Amt (A) = Cost1 * Share1 * Sales Tax So Use tax Amt (B) = cost1 * share2 * Use tax
same calculation for all the costs and then total cost with Sales tax = Cost 1 + A , Cost 2 + A and so on.. and total cost with Use tax = Cost1 +B, Cost 2 +B etc.
So there are around 14 new fields required to save Sales Tax amt for each cost, another 14 new fields to store Cost with Sales Tax, Cost with Use tax. So that increases the table size. Some of these fields might be used for making reports.
I was wondering which is a better approach out of the below 4: 1) To calculate these fields dynamically while displaying them on the User interface and not save in DB (while making reports, again calculate these fields dynamically and show), or 2) Add new formula field columns in database table to save each field, which would make the table size bigger, but reporting becomes easier. 3) Add only those columns in database on which reports needs to be made, calculate rest of the fields dynamically on screen.
4) Create a view just for reports, and calculate values dynamically in UI and not adding any computed values in table.
I am creating a simple application form using visual studio 2015. I can create database.mdf successfully and create dbo.table successfully. but when i tried to view table by expanding the table icon on the server explorer, the table should be able to show list of table but it didn't show any record and why is it like that.
1) CustomerID 2) FirstName 3) MiddleName 4) SurName 5) Title 6) Marital Status 7) Education 8) Occupation 9) Annual Income 10) Line of Business 11) DOB 12) Father Name 13) Mother Name 14) SpouseName 15) Gender 16) Email 17) MainTel 18) Home Tel 19) Passport Number 20)---------------------- 21)- - - - - - - - - - -
100)------------------- Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.
We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks). A single address includes address1/address2/city/country/state/pincode fields. In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.
As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)
Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.
Hi,Facts:I created a database to support an application that tracks events ondifferent objects. The two main tables are tbl_Object andtbl_EventLog. Each table has unique ID and on the tbl_EventLog thereis FK for a record in the tbl_Object. The events are inserted all thetime for the same or different objects from the tbl_Object. There areabout 600,000 objects in the tbl_Object and 1,500,000 (and growing)events in tbl_EventLog.Question:The user often wants to know what the last event was for a specificobject.What is the best way of retrieving the last event?Should I simply do a max(eventdatetime) on a specific object? orShould I add a LastEventID column to tbl_Object and update it everytime a new event is inserted? or any other way to implement it?I chose the second method because I didn't think it made sense searchthe event table everytime the user wants to know the last event, but Iwanted to know what the experts thought.Please let me know what you think.Thank you,Oran Levin
I would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).
What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:
I know how to generate scripts that would contain insert queries of all records in all tables. But how can I limit this to top 10 records only from each table?I can select "Data Only" when generating the script, but it generates insert queries for all data. How can get the script to have only top 10 record insert statements? These top 10 should be ordered by the primary key.
Hi, anyone could help me on how to do the inner join of two table from two different database. Below is my scripts: Dim sqlconnection1 As New SqlConnection(ConfigurationSettings.AppSettings("appDSN")) Dim sqlCommand2 As New SqlCommand("", SqlConnection1) Dim sqlconnection2 As New SqlConnection(ConfigurationSettings.AppSettings("smDSN")) Dim sqlCommandSM2 as new sqlCommand("", sqlconnectionSM2) Sub Page_Load(Source as Object, E as EventArgs) sqlCommand2.CommandText = "Select * from invitation inner join Guest on invitation.guestid = Guest.Guestid inner join Department on Department.departmentcode = Guest.deptCode where eventcode = '" & ecode & "'" sqlconnection1.Open() ............................ The invitation table is from SqlConnection1 and the Department table is from the sqlconnection2. How to do this . Pls .. pls help me. Ann123
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. How to design the table..
Product name : Nestle milk | Rainbow milk packages : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos | CTN=8 Nos
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:
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!
I am new to this type of programming and and have read all articles on adding an image to the database and it seems they all use sql queries to add an image but I want to add an image at design time. I am using Visual Basic 2005. I am also using Visual Basic 2005 Express Edition to try the same thing. I am trying to build a Translator program for english to Brazilian Portuguese and the reason I want to add the images is so that when I translate the word cat from english to Portuguese, I can also show an image of a cat. Can anyone please help me
We need to Insert/Update a Fact Table from staging Table. currently we are using a SP which update Fact Table for Each region. this process is schedule, every 5 min job is run and Update fact table.but time of Insert and Update too long from staging to Fact, currently we are using merge statement for Insert and update.in my sp we are looping number how many region we need to update and at a time single Region we are updating using while loop in current SP.
I have a requirement of table partitioning. we have 10 years of data on a table which is 30 billion up rows on 2005 server we are upgrading it to 2014. we have to keep 7 years of data. there is no keys on table or date column. since its a huge amount of data and many users its slow down the process speed. we are thinking to do partition on 7 years for Quarterly based. but as i said there is no date column on table we have to use reference table to get date. is there a way i can do the partitioning with out adding date column on table? also does partition will make query faster?
I have think three ways to do it. 1. leave as it is. 2. 7 years partition on one server 3. 3 years partition on server1 and 4 years partition on server2 (for 4 years is snapshot better?)
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?
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 ?!
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
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
I have to tables like given below Landing table "A" (Data load will happen over here, No primary keys mentioned over here) table "B" .Now I want to move the data from A to B.I have made use of below query insert into B select * from A...Landing table "A" has huge no of records, MS SQL server is taking huge amount of time.any alternative way to make this insertion process faster?
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
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.
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.
Hi, I am developing an application to a garment factory. I have a doubt in designing a table.Basic tables:Jobs, JobColors, Material, Units, Currencies ...These tables are designed with normalization rules.I got a problem at PurchaseOrderDetailsMain table is JobMaterial. It has materialid, jobid, supplierid, description and TypeFactor(which represents the type of order) means that the material is ordered based on size or colors or total qty.1 for ByColor, 2 for BySize, 3 for ByQty, 4 for By Contrast colorsThe main problem at the details of the sub table.JobMaterialDetailsIf typefactor is by size, i need to store the details based on sizeex: S - 2000pcs, M - 4000pcs, L - 4000pcs, XL - 2000pcsSo I will have 4 records per each sizeIf it is by color, White - 3000pcs, Portabella - 5000pcs, Black - 2000pcs.If it is by general, Total qty 10000pcsHow can I design this table. If I take, ColorOrSize column, it will refer different values for diffrent typefactor. When by size, it will have Size and when by color, it will refer colorcode.But colors are having referential integrity. So it is violated other than by color as typefactor.What is the best way to design this table?Can anybody suggest?Thanks in advance
I am desingning a table and i have a column OrderID and another column call Order, is neccessary to use a primary key, because One OrderID may have many Orders? Thanks.
Coming from a support background and having to design my first database I have a couple of questions re- table design. Firstly I have set up several tables and included one field (of the same name) in each. This is a primary key in one table with an incremental seed. I would like this info to appear in the other tables although these can be duplicates in the other tables. How is it best to achieve this relationship. From reading it suggest FK in the relationship application but looking at other databases this seems to have been achieved by some other means. Is it more common to use stored procedures to enforce this? If so please add pointers. Secondly, I have set up a couple of master tables to act as looks ups for fields in other tables. Again how do I get this to look up the table - is it done through stored procedures or at the time of writing the front end application?? Sorry if this is all basic stuff but it is new to me.
I have created a table with above column width. The rowsize is more than 8kb.And the table holds millions of rows of data. So is it a correct way of designing the table? Or how can I redesign this table.
I'm currently developing a real estate system to manage order processing and work flow. I'm a little uncertain as to how to design the tables because an order can have N number of applicants, owners, buyers, and properties. There are cases where there are 9 different buyers and some where the number of properties exceeds 20. It seems that normalization might make the situation crazy, but I'm a touch rusty. Thanks.
I am developing an estimating application and at the "quantity takeoff" level, all products/equipment/sub contracting/etc.... end up on the same table because of the common elements:
Item | Quantity | Unit Price | SubTotal | Total
Now, since the unit price of a product is not determined from the same tables as the unit price of equipment rentals or sub contract work, etc... how do I direct the attention to the appropriate table when trying to obtain the unit price or edit the unit price etc...
If my question is not clear, let me know, I will try to clarify.
