Friends,
Who is responsible for the Design of Database? System Analyst, DBA, Databse Designer, Project Leader? Coz I am working as a System Analyst, but now desgining the Databse for the ERP package which I feel is another man's work. Confussed. Plz help me.
Anil
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 RelatedHi all,
Does anyone know how I can design the database schema. I mean what tools can be used to the design the database and view the table relationships, etc. TIA.
Vik!
One interviewer has asked me the following question:
What are the things that you consider while designing database?
I have told about integrity constraints, and normal forms.
but he has added 15 more concepts like
1. indexers
2. Table columns
3. Table rows
4. search facilities
6.......
Can any one give full Idea on this question?
Thanking you Ashok kumar.
Hi,
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
My frontend scripting language is php
Thanks d'advance
Celia
Case 1:
A company is involved into e-commerce..hosting multiple websites for different products.
CAse 2:
The above scenario could also be implemented with a single site having multiple products for sale.
For Case 2 one would go for a single database for all the products.
While for CAse 1 ,a separate Database is developed for each Site.
What I fill is CAse2 is a more appropriate choice even if we have multiple sites for different products.
This would help us in rapid development of any ecommerce site...
ANd better ERP management for the Company.
I would appreciate some expert guidelines for the above scenario
Thanx in Advance
Warm Regards
Girija
Hi...
I am making a online job portal....for dat i need to make a gud database.
wud anybody be kind enuf to provide or tell me how to make a good database design...
thnx..
Dear All,How to reach to the highest level of normalization for database designing?Guide Lines Needed.What will be the characteristics of a database of a completely normalized databae?Check List needed.ThanksSuryaPrakash Patel****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...118039e018ee088*****************************************
View 2 Replies View RelatedDear ReaderI am trying to design a database. How can I make best Judgement that Indexing (which I am trying to fix during Diagram Desingning process)is ok.I am able to identify the best candidate for the indexing.Below is the details I want to understand:AreaZIPCityCountyDistrictState/ProvinceCountryNow I want the data retrival optimization through Index. (you can suggest another idea, also)Entities Area,...., Country have independent tables.Example:Area_TableAreaID (PK)AreaThey have relationship- one to many- if you go from Country to Area.There is one more table:Location_Table (PK)LocationIDAreaIDZIPIDCityIDCountyIDDistrictIDState/ProvinceIDCountryID(Location_ID is further related to the Address of the contact.)GUI has a single form to enter these details.On a save command details in all the tables -Area to Country- (individually) being inserted.& simultaniously Location_Table is also being inserted with the details.Following is the situation of being queried these tables:(1) GUI user can select an Area than the related details of ZIP .., ..., ...upto Country etc. should be loaded automatically (id it is previously stored by the user entry in the database.)(2) Contacts have to be retrived on the basis of Area, ZIP, .....County. (Necessary Groupings are required )Example:If Contacts are queried Country Wise then the Display should beCountry1State1District1County1City1ZIP1Area1Area2ZIP2City2County2District2Country2Please Guide.SuryaPrakash****************************************** This message was posted via http://www.sqlmonster.com** Report spam or abuse by clicking the following URL:* http://www.sqlmonster.com/Uwe/Abuse...0255a1765491f15*****************************************
View 5 Replies View RelatedHi All,
I am attempting to create a Visual C++ application based on displaying financial charts and am using SQL Express to store Stock information such as the Exchanges the stocks are traded on, the indicessectors they belong to and the Closing prices for as long as I can download data for. I am not proficient in C++ nor SQL and am using this project to learn both languages as well as making myself rich beyond my wildest dreams.
I have "designed" a database with the following tables:
tblDate_ 1 column clmDate (Primary Key, smalldatetime, NOT NULL)
tblStockExchange_ 4 column clmStockExchangeID (PK, int, NOT NULL)
clmParentID (int, null)
clmStockExchange (nvarchar(50), NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
tblMarkets_ 1 column clmMarkets (PK, nchar(20), NOT NULL)
tblIndices_ 1 column clmIndices (PK, nchar(50), NOT NULL)
tblSectors_ 1 column clmSectors (PK, nchar(50), NOT NULL)
tblMarkets_Sectors 3 columns clmMarkets_SectorsID(PK, int, NOT NULL)
clmMarkets_ (FK, nchar(20), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_ 4 columns clmEPIC (PK, nchar(10), NOT NULL)
clmSecurity_Type (nchar(5), NOT NULL)
clmSecurty_Name (nchar(50), NOT NULL)
clmSectors_ (FK, nchar(50), NOT NULL)
tblSecurities_Indices 3 columns clmSecurities_IndicesID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmIndices_ (FK, nchar(50), NOT NULL)
tblSecurities_Date_OHLCV 8 columns clmOHLCVID (PK, int, NOT NULL)
clmEPIC_ (FK, nchar(10), NOT NULL)
clmDate_ (FK, smalldatetime, NOT NULL)
clmOpen (float, NOT NULL)
clmHigh (float, NOT NULL)
clmLow (float, NOT NULL)
clmClose (float, NOT NULL)
clmVolume (float, NOT NULL)
Why so many tables? perhaps you should put some more in...
This was the only way I could work out how to store one-to-one and one-to-many relationships required for:
- Many closing prices for many stocks
- Stocks belonging to many indices
- Stocks belonging to only one sector
- Stocks belonging to only one market (MainMarket or AIM for LSE)
- Stocks belonging to only one Exchange (I am aware of dual listed stocks but one thing at a time)
Why nchar's and not nvarchar's?
Because I didn't realise the benefits of nvarchar's until recently. How can I change this a loose the extra spaces in the cells.
Why do some tables have IDs and others don't?
I decided to put ID columns in for tables that didn't have obvious Primary Keys - if someone could explain the advantages if ID columns I would be grateful.
To the SQL Professional's eye there will be some obvious things wrong with this design and your criticism is welcome. The database I have is achieving what I would like it to do; I can plot charts using the data but I have ran into problems when trying to create a TreeView control which is what I would like to use as a navigational tool in my application.
It would seem that pulling hierarchal data from a relational database, to pass to the TreeView control, is a tricky task to say the least. I have found many articles online which discuss how to do this (using an Adjacency List Model or Nested Set Model) but they define a fairly simple example at the beginning (based on fruit or electrical goods) but don't appear to talk about gathering data from an existing relational database or changing an existing relational database so that it is more suited to storing hierarchal information. I have Joe Celko's - Tree and Hierachies in SQL for Smarties but sadly this fine material is a little beyond me!
I would like the hierarchy to look like this:
StockExchange
Market
Sector
Stock
Indices
Sector
Stock
I have written three queries to get the StockExchangeMarketSectorStock information individually from each table but am struggling with ways to put all the rows together, add left and right values (Nested Set Model) then run queries against this to get individual nodes to pass to the TreeView control. Therefore is there something I need to add to the original design?
Any help would be greatly appreciated.
Hey guys,
So I started an SQL CE database for use inside a mobile application. I used SSMS to create a .sdf file (because if I let Visual Studio do it, SSMS can't open it because it will be version 3.5).
The reason I wanted to use SSMS is because I wanted to be able to design the database in design view, and populate it with initial information for the application to use. That means adding tables and rows.
The problem is, SSMS doesn't seem to have a design view available when working with CE databases. It also doesn't seem to have a feature for adding rows into tables. It's essentially as useless as VS2008 for designing my mobile database. It only lets me add tables, and I can't even do that visually in design view. I have to use those cumbersome forms.
Is there any way to design a CE database in design view? And add rows of data into tables? It feels like I'm overlooking something.
Note: I dont need to subscribe or publish the database. The mobile application just needs to use the database as a repository.
I am trying to build something similar to www.alienware.com where it lets you build your own computer. I was wondering if some one could help me design sturcture to do it on my own. I am zero in DB and know little asp. I am trying to do it for my own site.
Thanks
How can I design reports for Reporting Services without using Visual Studio.NET?
I have an SQL Server 2000 with RS but I dont have a tool for designing reports.
Thx for help
Dear Friends,I'm a junior DBA,
I've to prepare an online examination.
for this, I've three categories.
a)beginer level
b)intermediate level
c)expert level
again here subjects are 6. like sqlserver,oracle,c#,vb.net,html,javascript.
in these subjects, i've to select these three types of questions.
now how can i design for this requirement? shall i create three tables for beginer, intermediate,expert or shall i create 6 tables and write according that?
am i given correct inputs?
please give me an idea to design
thank you verymuch experts.
Vinod
I am new to sql server. Currently i am using sql server 2000. i have created two tables course and student , with course_id and stu_id , as primary key of the respective tables.
Now i am trying to relate them through the diagram , in order to create a relationship between the two. When i try to draw a relationship between stu_id and course_id , i get the following error
error: Unable to create relationship 'FK_stud_course'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_stud_course'. The conflict occurred in database 'student', table 'course', column 'course_id'.
Im creating a sample database for purposes of keeping track of employee's jobs + billing codes for that job. currently they just use an excel spreadsheet to keep track of billing.
Question 1:
What would be the easiest way for the manager to enter billing codes? Billing codes are numbers like 956, 958, 805 and they co-relate to prices for each billing code.
1. I want the manager to enter the billing code and have the sql database find out how much that code is and add them up daily. This would prevent the manager from having to input prices and billing codes, and extra step.
Any links on the above topic would be helpful. I'm not looking for anyone to spoon feed me code just point me in the right direction. Im relatively new to sql having only a class of sql @ ITT.
Heres the way I have the db tables laid out currently.
employee table
employee_id
technumber
fname
lname
address
workphone
homephone
trucknumber
officelocation
employeejobs table
employee_id
jobs_id
jobs table
jobs_id
codes
date
timeframe
city
hours table
hours_id
employee_id
hoursworked
month
Any info would be much appreciated, Im going to go find a sql book now ;).
Hi All,I have a table below and I want to design a query to pull all themembers from the TABLE into a Query Result and into a single column withpoints assigned appropriately, but I am having a lot of difficultiesdoing this. Any help is greatly appreciated.TABLEMEMBER1 MEMBER2 POINTSJoe Don 2Macy 1Jack Nick 2Joe Rob 2This is a result I would like to generate from the queryQuery ResultMEMBERS Total PointsJoe 2Don 1Macy 1Jack 1Nick 1Rob 1Thank you,Jim*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi
I am SSIS newbie and need help in desigining this flow.
Source Sqlserver2005
Select records based on complex sql statement from a [InstanceA].[DatabaseA].[TableA]
Target Sqlserver2005
Insert the records into another table [InstanceB].[DatabaseB].[TableB] only if these records are not present
Take the records from [InstanceB].[DatabaseB].[TableB] and insert it into
[InstanceB].[DatabaseB].[TableC] only those records which are not in C
And finally
Take the records from [InstanceB].[DatabaseB].[TableB] Join them with
[InstanceB].[DatabaseB].[TableC] and insert it into
[InstanceB].[DatabaseB].[TableD] only those records which are not already in D
Can somebody please help me in visualising this solution .
I am having problems populating a target and then using that populated
target as a source for subsequent targets.
regards
Hrishy
HI gurus,
I need help with database design. I am doing a bowling league program.
1. each bowling center has 1 or more leagues
2. each league has 2 or more teams
3. each team has 2 or more bowlers
4. each week each team bowls 3 games
5. summer leagues last ~13 weeks
winter leagues last ~36 weeks
Tables I have + is primary key FK is foreign key
BowlingCenter
+ CenterID
......
League
+ LeagueID
FK CenterID
.......
Team
+ TeamID
FK LeagueID
Bowler
+ BowlerID
FK TeamID
Here I am lost. How do I do the 3 games a week for 13 or 36 weeks AND associate the 3 games each week with each bowler?
Any help would be appreciated.
Hi, not sure if this is the right forum for this question.
I am creating relational tables for the first time in sql server express. I will have an orderItems table and an orders table. the MenuItems table is the problem. It is a catalogue of books. There will be about ten columns. all are unique to each book. i.e isbn number, title, author, publisher etc. but ten columns seems to be quite cumbersome. it may be easier to break the table down into two tables (i.e. primary details and secondary details perhaps) However to populate the table in the first place it would be easier to have it as one table instead of opening and closing 2 tables Adding the odd book to the two tables in the future would not be a problem. so the question is can i create a table and then brak it into two relational tables afterwards. If so how do i do this. this is my foirst go at relational tables and i am still trying to get a handle on visualising them. If my logic is up the wall please let me know....
Nick
Hey,I have a fairly large table for my keeping my information about users. My question is, would it be better to separate it, for example, create another table that has all the personal information (like city, street, etc)and have it related to my other table where more of the data-ish information is kept? Thanks,Sixten
View 3 Replies View RelatedHi,
I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:
[prodID][qty][price]
That is to attch a collection of products to one order entry.
Thank you,
shlomi711
With my next application, I'm thinking about establishing a new security paradigm for my programs, with respect to SQL Server.
In all my previous applications, connections are established using SQL Server authentication. So, all my users may log-in under a single log-in, mapping to a single user in a database. My application then has to use its own security arrangement to determine who has access to what forms within the application. There are three major problems with this design: 1) the user name and password to connect to the SQL server have to be resident as strings somewhere in the application code (or ancillary files), 2) everyone connecting to the SQL Server "looks" the same, and 3) you have to give the broadest rights to everyone with the same login and then pare those rights down within the application itself.
By implementing multiple SQL Server Authentication logins I can mitigate problems 2) and 3), but the only way to eliminate problem 1) is to move to Windows Authentication.
Windows Authentication would allow me to resolve all three design constraints, but there is one problem that I see coming as a result.
If I use Windows Authentication, each user must have an independent login to the SQL Server. If I have an application that may have 4,000 to 6,000 users, does that means I have to have 4,000 to 6,000 logins set up on SQL Server?
Is that true?
I could, of course, generate a script to build all 4,000 to 6,000 users, but I am concerned about this.
Is this a "normal" arrangement that SQL Server has no problem-with?
I'm currently developing an ASP.NET site with SQL Server 2005 Standard and I'd like to ask a question about the future of the database. It needs to have continuity and performance. I'm thinking about doing replication or mirroring for continuity and table partitioning for performance. I admit I've never done any of those before and I'll learn about them but they're not needed at this time. The question is, I'm currently designing the database and do I have to anything for consideration for those things I'm thinking of implementing later? For example, I'm using Identity in my tables but I've heard about identity crisis using replication with identity columns, therefore I'm thinking of using Guid's but now I fear the Guid column index itself will be the slowdown factor in the first place.
Any suggestions to consider? I'd appreciate any opinions.
Hi i am designing a database. It will be used by field guys who will insert new records and will replicate at the end of the day with a central database using merge replication. Can anyone tell me if I am choosing wisely or not with the following 3 points:
1. i am going to use a uniqueidentifier value in a column (using newid()) for the merge replication. (I don't care that it's an ugly value as i never plan to look at it.
2. I am using an identity column (INT)to create a unique value within the table
3. I want to create a unique column comprising of data from other columns (e.g. date+identity+salesrep) This gives me an intelligent candidate for a primary key. But I think i have to create this with an instead of trigger (is that right)
many thanks in advance
ICW
I have a flat /derived table of orders that customers made, virtually
order_tbl
(
order_id
,item_id
,product_id
,product_price
,product_name
,customer_id
,customer_name
,order_price
,order_tax
,order_date
,order_year
,order_month
,order_day
)
and I would like to have:
1.update on this derived table daily (how/what should I do - with triggers/jobs against the original tables?)
2.create a view that would show:
- by years the percentage of sold products
- quarterly percentage of new customers / rebuyers / etc.
- ...
3. Thinking of making a cube in BIDS (with dimensions / measures)
Any help is appreciated. :-)
I want to design a login named "JobAdmin" who can add/modify/delete any SQL Agent Jobs but can't access my "db1" database. The jobs would execute some stored procedures in db1, and it could be a user "db1dbo" with db_owner role in db1 to execute the stored procedures.
How could I let JobAdmin impersonate db1dbo to run these jobs? Thanks in advance.
i have created a report which contains a table(header,group header , details)... when i get a copy in pdf format i got more than 20 pages...the issue is the table top border is not displayed in all pages except first page...
for example
if i have two pages ..
first page top border is always displayed properly ..second page top border is not displayed properly.. i think this issue because of alignment , i have tried a lot of scenarios nothing works ...
can any one help me
Hi,
I am asking something very basic and theoretical, here.
Are there any design tools available in the market for designing SSIS packages?
By "design tools" I mean tools which enable us to "plan" or "design" the architecture of a SSIS Solution that will be implemented later, using SSIS, of course.
We have several design tools available for designing a Web Application solution, for example. Similarly do we have something for SSIS?
Are there any design approaches, best practices and/or design techniques published for designing a SSIS solution?
Please note that I am not talking about the SSIS Designer or the BIDS. I am talking about a tool/approach for designing the SSIS solution which can be delivered as a project artifact before the actual coding phase starts?
Well, I have tried to express myself as best as I could.
If someone can help me with this, it will be really great!
Thanks in advance.
Regards,
B@ns
Hello
We are trying to create an app where we pass dynamically a dataset (from our form in C#) to our {report}.rdl file.
We are having doubts about something.
How can we design the actual report if we don't have the datasource until runtime? The reason to do this is due to our complex calculations of data which becomes almost impossible to achieve in a T-SQL environment.
I know with a lot of patience and time (we don't have both) we can achieve it, but even though, we would need to process some data in the client side.
So, the question is... is it possible to design a report without a Dataset? I know we can drag the controls on the layout window, but we won't be able to test it. IS there any workaroung about this?
Thank you
I have been advised to design multidimensional model based on the operation data model.
What I have is ----
Couple of documents about the application.
Data model of operational data.
Now I have been told to create a datawarehouse from which any type of wuestion can be answered.
This seems horrendous.
Please guide.
I have created an application that I intended to be 3-tier, but I am not sure if I did it properly. I constructed it like this: I created a DLL that contains methods that validate the passed parameters, checks the data against business rules, and issues ADO.NET methods to access the data. The ASP.NET presentation layer uses Object Data Sources to link to these methods. With this architecture I consider the ASP.NET pages to be the presentation layer, the DLL to be the business layer, and the database itself to be the data layer.
Now I am wondering if the standard practice is to have a further division? In this case, there would be a business layer DLL whose only purpose is to validate the parameters passed to it by the presentation layer, and to do business rules checking. There would also be a data layer DLL whose purpose is to accept parameters from the business layer and issue ADO.NET methods to access the database. In this scenario the database itself would be considered part of the data layer, or not considered to be a layer at all.
Either one will work, but I would like to implement the architecture that is most accepted, and allows the easiest maintenance. What is the best practice for designing a 3-tier architecture?
Hello
This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
take care,
~Iris~
:)