Designing Relational Tables
Sep 22, 2007
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
View 2 Replies
ADVERTISEMENT
Dec 25, 2007
hello,
I am beginner for asp.net and sql server. I used Sql server manegement studio full version and I exported my aspnetdb which was created by VS2005 to my host sql server. I have a question:
relational tables are not relational no longer. I noticed that when I created database diagram. what is wrong by exporting?
thanks for your helps...
View 3 Replies
View Related
Jul 18, 2007
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.
View 3 Replies
View Related
Sep 22, 2004
Hi,
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
View 1 Replies
View Related
Mar 20, 2008
Can Anybody explain me this how to Do this ?
Is this done by definning the Primary key and foreign key constraints ?
View 1 Replies
View Related
Apr 7, 2008
I understand the basics for doing an insert. Their seems to be many ways of achieving the same thing in .net. I have used the tableadpaters and sqlcommand functionality to achieve this. My one question is how foregin keys should be created in associated tables. EG so if i create a new record entry in one table and the primary key for that entry is a foreign key in another table, do I need to call 2 table adapters, or run 2 sqlcommands(taking the primary key from the first traction and use this in the next transaction)?
Any help or direction for some good tutorials on this would be much appreciated.
Thanks
Mark
View 4 Replies
View Related
Mar 4, 2008
Hello friends .....What is the answer for this question in frame of SQL 2005 Management Studio or SQL Server 2000 ?
What are the properties of the Relational tables?
View 1 Replies
View Related
Mar 7, 2007
Ok, I have a page on my website where we can add products to our database. We are a music store, and most products have different versions or colors. I've created 2 tables, Products and Subproducts. The products table may hold info like Fender Stratocaster, and the subproducts would hold colors (Blue, Sunburst, etc). The subproducts table has an integer field called MainProductID, which is linked to the mainproducts table field RecordID. So far the page uses a wizard where if first creates the main product using an sql datasource. After the data has been added to the main products table, my page gives you the opportunity to add different sub products. The problem I am having is actually feeding in the RecordID from the main products table to my insert parameter on the sub products data source. This is what I have tried so far: There is a formview on the page that is bound to the main products table, after the entry is created I can physically see the info on my screen, so I know the data is there at my disposal SubProductsDataSource.InsertParameters.Add("@MainProductID", Formview1.datakey.item("RecordID"))SubProductsDataSource.Insert()Using this adds the data to the table, but the MainProductID is nullalso is there a cheap little way to refresh a page, because when I upload the product images I have it go to the next step where you are supposed to be able to see the images you uploaded, I don't see them which makes me think that the page is loading faster than the images are uploading. Thanks
View 1 Replies
View Related
May 2, 2008
Hi!
I am trying to insert data into 2 different tables. I am using dataadapter and dataset.
Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SubmitButton.Click
Call ConnectionString()
Dim insertSQL As New SqlCommand()
insertSQL.Connection = sqlConn
insertSQL.CommandText = "SELECT location.CountryName, location.CityName, location.BuildingName, location.FloorID, rooms.name, rooms.FloorID AS Expr1 FROM location INNER JOIN floors ON location.FloorID = floors.id INNER JOIN rooms ON floors.id = rooms.FloorID"
Dim ds As New DataSet()
Dim da As New SqlDataAdapter()
da.SelectCommand = insertSQL
Dim scb As New SqlCommandBuilder(da)
Try
da.Fill(ds)
Dim ndr = ds.Tables("location").NewRow
Dim ndr2 = ds.Tables("rooms").NewRow
ndr("FloorID") = FloorIDDDL.SelectedValue
ndr("CountryName") = CountryNameTextBox.Text
ndr("CityName") = CityNameTextBox.Text
ndr("BuildingName") = BuildingNameTextBox.Text
ndr2("name") = RoomNameTextBox.Text
ndr2("FloorID") = FloorIDDDL.SelectedValue
ds.Tables("location").Rows.Add(ndr)
ds.Tables("room").Rows.Add(ndr2)
da.Update(ds)
ErrMsgLbl.Text = "Information saved successfully"
Catch ex As Exception
ErrMsgLbl.Text = ex.ToString
End Try
sqlConn.Close()
End Sub
The above code does not throw any error. It also does not update the tables.
Your help will be appreciated.
Thanks!
View 5 Replies
View Related
Jul 16, 2012
I have two tables with data that I need to get and display in a combobox. What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.
This SQL seems to be more complex than I have done previously
I can get all of the records from both tables easily:
Code:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)
However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)
View 2 Replies
View Related
Aug 8, 2007
I've got a lot of XML like this (simplified):
Code Snippet
... 8 MORE
... 9 MORE TIMES
I need to get this into three existing SQL Server 2005 tables, each with identity columns for their primary keys:
Code Snippet
CREATE TABLE ELEMENT1 (
[ID] INT IDENTITY
)
CREATE TABLE ELEMENT2 (
[ID] INT IDENTITY,
[ELEMENT1_ID] INT
)
CREATE TABLE ELEMENT3 (
[ID] INT IDENTITY,
[ELEMENT2_ID] INT
)
With primary and foreign keys as you'd expect, and, of course, many more columns!
How would I get this into tables through SSIS, preferably in a high-performance manner (there may be several gigabytes of XML to load).
The issue, of course, is that in order to insert an ELEMENT2 row, I need the ID from the coresponding ELEMENT1, etc.
Any ideas or pointers to articles would be welcome.
View 14 Replies
View Related
Oct 7, 2015
I'm shredding the below xml into relational tables. Each element of the xml has it's own table and there is a foreign key to join the tables, you can see this in the below picture. The process I follow is each relational table I always bring the nesecary xml and store it in the table and when shredding I always look at the parent table.So for example when processing the seat table, I use seat xml from the parent route table, also taking the ROUTEID from the route table. The reason I do this is all about taking the id from the previous step to create the relationships between the tables. without taking the xml down to the tables?The problem with this approach is I have xml stored in most tables and the tables are becoming very large.
<Route Type="OneWay" >
<Seat Type="FirstClass">
<Prices>
<Price Price="10" />
<Price Price="11" />
</Prices>
[code]....
View 4 Replies
View Related
Aug 4, 2005
Hi all,
I am trying to create a diagram for our database, during the creating, I create some of the relationships which were not there(basically our original database is not relational database, that's why I am doing it)
So sometimes I have to chage data type in order to create a relationship for the coloumns in different tables. i.e. change char(16) to varchar(7) (I checked the field that make sure all the data in this field is <= 7 characters)
But when I saved the diagram, there is an error message that state:
Errors were encountered during the save process. Some of your database objects are not saved on your diagram.
'agent' table saved successfully
'VisitUSA' table
- Unable to create relationship 'FK_VisitUSA_agent'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_VisitUSA_agent'. The conflict occurred in database 'CMC', table 'agent', column 'AgentCode'.
What does that mean? is it caused by some of the agentcode data in VisitUSA table which is not in agent table?
Thanks!
Betty
View 3 Replies
View Related
Feb 26, 2004
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
View 3 Replies
View Related
Apr 18, 2006
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
View 2 Replies
View Related
Apr 5, 2004
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
View 14 Replies
View Related
Mar 8, 2007
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
View 2 Replies
View Related
Aug 22, 2007
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'.
View 1 Replies
View Related
Sep 21, 2007
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 ;).
View 1 Replies
View Related
Jul 20, 2005
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 Related
May 9, 2008
Hi
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
View 6 Replies
View Related
Feb 13, 2008
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 Related
Jun 9, 2004
Hi 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!
View 1 Replies
View Related
Dec 5, 2005
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.
View 1 Replies
View Related
Nov 22, 2005
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
View 1 Replies
View Related
Apr 24, 2008
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?
View 8 Replies
View Related
Feb 26, 2004
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
View 1 Replies
View Related
May 14, 2008
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.
View 5 Replies
View Related
Mar 7, 2006
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
View 5 Replies
View Related
Mar 19, 2008
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. :-)
View 1 Replies
View Related
Jan 7, 2007
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.
View 7 Replies
View Related
May 16, 2007
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
View 2 Replies
View Related
Nov 7, 2007
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
View 10 Replies
View Related
