Database Design/query Design
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
ADVERTISEMENT
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
Oct 2, 2005
I am designing an ASP.NET app that can be used to keep track of
attendance at office hours for a class. The purpose of this is
that we need to know if a student is attending office hours by
different people (so that we can flag them as "in trouble"). I
don't know if I have chosen the best database design, and I'm lost as
to how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA
char
4
n
Date
smalldatetime 4
n
Start
smallint
2 n
End
smallint
2 n
Student1
bit
1 y
Student2
bit
1 y
Student3
bit
1 y
Student4
bit
1 y
Student5
bit
1 y
I chose to have the students as columns because the students don't
change, and then you add rows of office hours. If students are
the rows, then you would be adding columns as the semester continued
which I thought was odder...? I'm completely open to suggestions
on Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (and
also the first one besides select * from HoursAttendance) is that I
want the names of Students who have attended more than x office
hours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'm
relatively new to design, so constructive criticism is desired please
View 7 Replies
View Related
Aug 18, 2015
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
View 3 Replies
View Related
Aug 20, 2007
table a
id pct
1 0.1
2 0.24
3 0.5
table b
year amount
2001 10
2002 20
2003 30
2004 23
2005 67
2006 80
I want to create a table ret
wiht this
year newamount
2006 80*0.1+67*0.24+23*0.25
2005 67*0.1+27*0.24+30*0.25
2004 23*0.1+30*0.24+20*0.25
2003 30*0.1+20*0.24+10*0.25=9.9
How can i do that.
Thanks.
Jeff
View 5 Replies
View Related
Oct 27, 2007
hi
i am new to t-sql. the problem is i have to subtract a field from a column A in table X from a field in Column R from Table Y and put the subtracted value in the Column K in the Table Z
ex:
Table Name: Order
Field Name: Boxes
Table Name: Despatch
Field Name: Boxes
Table Name: Balance
Field Name: Boxes
the problem is i have to subtract the values(Boxes) in the table Despatch, from the values(Boxes) in the table Order, and the subtracted value has to be put in values(Boxes) in the table Balance.
can any one help?
one more thing: Where i can get the basics of t-sql
regards.,
sekar.r
View 2 Replies
View Related
Oct 2, 2007
Hello, I am trying to achieve a query results with not much luck. I am struggling the query design of how to construct to get the right results.
My table has the following data
RoomID Subject StartDate Duration(min) EndDate
201 test 28/09/2007 07:00:00 180 28/09/2007 10:00:00
202 test 28/09/2007 09:00:00 240 28/09/2007 13:00:00
The best way to describe is a scheduling system with the StartDate, Duration, EndDate.
I am trying to query for datetime periods which are allocated, eg the table records show from 7am - 10am and 9am - 1pm are allocated.
When I query on StartDate >= 28/09/2007 07:00:00 AND EndDate <= 28/09/2007 13:00:00, record 201 & 202 shows which is correct because they are within query parameters and are allocated.
When I query on StartDate >= 28/09/2007 07:30:00 AND EndDate <= 28/09/2007 13:00:00, record 202 only shows which is correct because record 201 StartDate is before 07:00:00.
But my problem is I require record 201 to show because the time period between 07:00:00 and 09:00:00 is allocated and the query parameter is 07:30:00 which is still between 07:00:00 and 09:00:00.
I have researched where on similar situations, the use of duration is used, but I am unsure on its application to achieve results.
I have also read of other situations where a lookup table should be used, but again I cannot get my head around the application of alookup table to query the resuults.
Does anyone have any theories, design thoughts or real work solutions they can help me with.
Thanks
Peter Smith
View 9 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
Dec 13, 2000
The database will provide content management for an Intranet site.
On the site we have a number of articles which are categorised. Each categories can also have sub-categories.
The relationships would seem to be
Category---->Sub-Category----->Article
A category has one or mant sub-categories. A sub-category has one or many articles.
However some categories have articles but are no sub-categories.
Should I create a further relationship between Category and Article to cater for this instance or should their be a dummy category record in this instance?
View 1 Replies
View Related
Sep 30, 2002
Hi everbody!!!
i first would like to congratulate this forum for the new member!!! ME ;)
Then i have this question, i´m going to design a database in SQL server and i would like to have some basic hint how to build it up.
It´s a calender for a ASP.Net site i´m building and for the moment it´s just 4 things to have in mind
-users
-dates
-todo´s
-birthdays
If something is unclaer then PLEASE ask me!!
Thanx in advance icaaq
View 3 Replies
View Related
Dec 24, 2002
Hi all:
I am designing a sql server database to keep track of patient history and data about various diseases. Is there a book or any kind of resource that I can refer? Any help is appreciated.
Thanks,
ndba
View 1 Replies
View Related
Feb 9, 2001
I had a consultant design a database for me and every table contains a GUID. Most tables already contain a field that is unique. Is this necessary? In my view, the queries are no more efficient and it just takes up more space.
Thanks
View 1 Replies
View Related
Sep 30, 2004
trying to work on a new project, i need some ideas to setup this design model.
sql 2000 database and asp.
This is call center environment where agent bid on shift (schedules) base on most disireable to least, and it process base on seniority.
View 5 Replies
View Related
Jan 14, 2005
Hi I was wondering if you could help me. I am currently trying to design a database. This database 4 tables, Main_Cat, Sub_Cat,SubSub_Cat and Prod_parent. How ever, not every sub_cat has a subsub_cat. And waas just wondering how I would link this.
Thanks
Ashley
I have attached an image of what I have so far, if any one could help it would be greatly appreciated
Thanks
View 1 Replies
View Related
Jun 29, 2007
hi
Actually i have been assigned a project in Sql server 2000 with front end in vb 6, and its an inventory control system of a computer retail company. i dont know how to make its design. it must show inventory items and new purchases and when they are made they must update the inventory and when the items in the inventory are taken to prepare orders recieved from the customers the inventory must be updated. it must also show customers back orders, daily expenditurers, monthly expenditures, staff handling the inventory, different brands ND JOURNAL AND LEDGER Accounts, journal accounts show daily expenses of a month and ledger shows monthly expenses and it must also show yearly expenses and net profit and the transactions made to the company accounts by the customers and when items are bought from the supplier it must automatically update the company's account when the comapny makes payment to the supplier.
Thanks...........
View 1 Replies
View Related
May 20, 2008
I need help to design a database.
The Scenario is:
There are 5 Departments
Dept1
Dept2
Dept3
Dept4
Dept5
Each department has 10 batches of students. The questions are stored in SQL Server 2000 Database. Each batch has 8 subjects.
The question for each batch are 200-400. Some questions has 4 options and some has 7 options to select multiple aswers. Some questions as more than 1 answers(Ans1, Ans2, Ans3). Some Questions also has 2-3 pictures. During test a student can select Check option to review its answer again. Database should be capable to hold 5000 users at a time.
My questions are:
How should I design database; should I design Department wise or batch wise or Subject wise?
And does database design will be different for a Web based application and Windows based application?
And Is Sql Server my correct choice?
thanks in advance
View 14 Replies
View Related
Apr 6, 2008
I've got a table of computers and each computer is assigned to a computer group.
I also have a table of content and a content item is assigned to multiple computer groups.
This gives the following structure:
Computer
---------
ComputerID int pk
GroupID int fk
ComputerGroup
---------------
GroupID pk
ComputerGroupContent
--------------------
GroupID fk
ContentID fk
Content
-------
ContentID pk
Now, this is the bit I'm not so sure about....
Each content item can have a number of attributes that can be customized by a computer. So for example, a piece of content has an attribue called 'Title' that each computer can provide a value for it
Thus here are the additional tables:
ContentAttribute
----------------
AttributeID int pk
ContentID int fk
Name string
AttributeValues
---------------
ComputerID int fk
AttributeID fk
Value string
So the issue I have here is that there's nothing to stop a computer from having content attribute values for a content row that is not actually assigned to the computer's group!
Does this suggest a poor database design?
Cheers, XF.
View 8 Replies
View Related
Jun 14, 2008
Hi,
I am new to databases and learning Entity Relationship Diagrams.
Can any one point me to a good site where I can view ERD for a shopping cart?
Thanks.
View 1 Replies
View Related
Aug 29, 2006
Hi all!
I'm beginning in SQL Server. I got a problem :
I need to design a database manage foreign currency and everyday it must be update.
I design a tables :
tbl_Currency includes : iCurrencyId, iOrder, cCurrrencyName, cUnit, mBuyingCash, mBuyingTranfers, mSelling, dDayUpdate.
But now people want to view this rate by day.
How can I do this? Create a new table to save old value or modify the current table?
Thank
View 4 Replies
View Related
Apr 3, 2007
Hello,
I'm new your site and this database design stuff.
I'm trying to design a databse for a school which I can register a student to school and then get information about his grades, his personal info. I make "student", "teacher" .. tables.
In the "student" table I also take the birth certificate info.
Then I also want to know about teacher's birth certificate info.
So the same columns in "student" table for birth certificate info also was included in "teacher" table.
Then I take birth certificate info columns(they depent on personal identification number ).
At the end I have "student" , "teacher" , "birthCertficate" tables.
So I have 1-1 relationship between student-birthCertificate ,
teacher-birthCertificate.
My question is : How much does make sensible this approach?
View 20 Replies
View Related
Jul 13, 2007
Hello everyone,
I have a situation where I have about 40 tables according to a database plan in which there maybe addition/deletions of columns in future.
I have upto 5 primary key's in some tables.
When a paticular column needs to be changed,I need to change the whole database due to the primary key's.
What is the best way to make things simpler?
Please help me,
Thanks,
Godwin
View 1 Replies
View Related
Aug 14, 2007
i want to create database to store product information and my application can create promotion of product
eg:
buy A get A free
buy A get B for 50%
thanks for help,
EK
View 8 Replies
View Related
Oct 24, 2007
I am designing the database.
I was told to create a table with nearly 120 fields..
and another two tables with 35 fields and 50 fields.
the data was stored to 100,000 (aproxmately) per year.Please suggests will it be a problem if the table contains 120 columns.
View 5 Replies
View Related
