Table Design Question - Subject Matter Expert Database
Aug 8, 2007
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:
tblEmployee
EmployeeID (PK)
LastName
FirstName
etc......
tblSubject
SubjectID (PK)
Description
tblOwner
SubjectID (FK)
EmployeeID (FK)
tblExpert1
SubjectID (FK)
EmployeeID (FK)
tblExpert2
SubjectID (FK)
EmployeeID (FK)
tblExpert3
SubjectID (FK)
EmployeeID (FK)
Does this make sense? Would I run into any issues when trying to display this on a form in VB?
Thanks in advance for any help!!!!!
Cal
View 8 Replies
ADVERTISEMENT
Apr 4, 2007
Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.
Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.
Further specification can be revealed if appropriate help is provided.
your advice will be of great help for me.
Thx
View 4 Replies
View Related
Aug 31, 2007
All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Thanks!
View 7 Replies
View Related
Jul 23, 2005
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
View 3 Replies
View Related
May 23, 2001
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables?
Any help would be greatly appreciated.
Thank you
View 1 Replies
View Related
Mar 16, 2001
Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?
View 1 Replies
View Related
May 23, 2001
I have two tables that I am trying to join and both have similar columns with which I am trying to use. One is Date and the other is LOGdate. Date's format is "01/01/2000" BUT LOGdate's is "01/01/2000 12:41:00 PM/AM". Can I join these two tables? If yes how do I get rid of the excess time on LOGdate.
Any help would be greatly appreciated.
Thank you
View 2 Replies
View Related
Aug 25, 2005
Here is the situation
Table 1 : tbl_documents
docIDdocName
1aaa
2bbb
3ccc
Table 2 : tbl_Rating
ratIDratingdocID
131
251
321
432
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating
1aaa3
2bbb3
3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As
RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID =
tbl_documents.docID”
PLs help me ?
Thx
View 3 Replies
View Related
Oct 23, 2007
OK so now I'm setting up a trigger to email info to different people based on values INSERTed into one of my tables. I'm using an IF statement to determine who I'm giong to send the mail to, but I don't really understand how to include the data (parameters) in my message. Thanks for any help. Here's what I have so far.
Code Block
ALTER TRIGGER [smallin].[trig_test]
ON [smallin].[DATALIST]
AFTER INSERT
AS
declare @rc int,
@post bit,
@pre bit
SELECT @post = [POST], @pre = [PRE] FROM inserted
IF @post = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is post data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'
ELSE IF @pre = 1
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = N'ln.li@mydomain.com',
@FROM_NAME = N'Alert Mailer',
@TO = N's.mallin@mydomain.com',
@replyto = N'ln.li@mydomain.com',
@CC = N'',
@BCC = N'',
@priority = N'NORMAL',
@subject = N'This is pre data',
@message = N'Goodbye MAPI and Outlook',
@messagefile = N'',
@type = N'text/plain',
@attachment = N'',
@attachments = N'',
@codepage = 0,
@server = N'exchange.mydomain.com'
View 5 Replies
View Related
Jan 31, 2005
I have asked a similar question on SQLTemp.com, but I thought I would ask here in case there are people here that don't visit SQLTeam and can help me.
I have to tables:
tbSiteworkPriceList
ItemID | Descr | Material | Labour | Travel | Boarding
-----------------------------------------------------
1 | Finishing | 0.25 | 28 | 42 | 65
tbSiteworkCostTypes
CostTypeID | Descr
-------------------
1 | Materials
2 | Labour
3 | Travel
4 | Boarding
Now, the tabels above are a sitework price list for an estimating package. The tbSitworkCostTypes table is used to trace the value of the particular item back to an account after the item is added to a takeoff. My questions are:
1) How should I tie each items variable (Material, Labour, Travel, Boarding) to its appropriate cost type?
2) Should I divide the table tbSiteworkPriceList above into 2 tables as shown below?
tbSitworkPriceList
ItemID | Descr
1 | Finishing
tbSiteworkCostItems
fkItemID | fkCostTypeID | Price
-------------------------------------------
1 | 1 | 0.25
1 | 2 | 28
1 | 3 | 42
1 | 4 | 65
This seems to be a little better in the point of view that each item variable is linked to the appropriate cost item by joining the tables tbSiteworkCostItems to tbSiteworkCostTypes. They have to be linked because after the takeoff is generated, the dollar values have to be imported into an accounting system where the account code and cost type code are determined by the "CostTypeID". Any thoughts?
Mike B
View 1 Replies
View Related
Mar 13, 2008
Hi All Professionals Programmers,
I would like to ask a question that is very important for me. The question is how can i create a flexible data base in which i m able to create the inner levels as much as i can.
Like i have a table building, then i have another child table floor, then the floor become parent and i have its child rooms, then the rooms become parent and i have its child floor tiles etc.
you can see i am going to inner dept, so i need a flexible database because its very costly and intimadting to change the database and every time create a new table and relationships.
Hope you have understood what i am going to say and need advice of professional and expert user to resolve it.
Any concise quality material like articles, white paper etc will also be suitable for me.
Thanks in Advance
View 3 Replies
View Related
Jul 18, 2011
I have a requirement like, we are having two tables in our database.
Table names: student, marklist
Student table values:
id studname
------------------
1x
2y
3z
4a
5b
Marklist table values:
id maths physics English
---------------------------------
1506070
2706040
3508070
45010070
5906070
But my requirement is, I need to display the data "subject wise" highest marks for each student.
for example:
id name highestmark
---------------------------------
1 x English
View 9 Replies
View Related
Feb 13, 2008
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
View 3 Replies
View Related
Feb 27, 2005
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
ticketID
ticketName
ticketDescription
DueDate
ticketRequestor *
AssignedTo *
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 ?
thanks
View 2 Replies
View Related
Jan 15, 2008
Hi all,
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.
Thanks so much for your suggestions!!
Dilla
View 5 Replies
View Related
Mar 23, 2015
I have questions table and answers in survey database. Now I design responses table from users !
CREATE TABLE [dbo].[Response](
[ResponseId] [int] IDENTITY(1,1) NOT NULL,
[SurveyId] [int] NULL,
[QuestionId] [int] NULL,
[AnswerId] [int] NULL,
[VoterId] [int] NULL,
[Code] ...
My Questions : How can I store data if questions is multiply answers (checkbox type) ?What is best way ?
View 16 Replies
View Related
Apr 14, 2006
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:
Sales.SalesOrderDetail
Purchasing.PurchaseOrderDetail
etc...
Why not:
Sales.OrderDetail
Purchasing.OrderDetail
etc...
????????
View 1 Replies
View Related
Jan 24, 2008
Hi
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.
Your help is greatly appreciated.
Thanks
View 3 Replies
View Related
Jan 24, 2008
Hi
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.
Your help is greatly appreciated.
Thanks
View 4 Replies
View Related
Jul 24, 2015
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.
View 4 Replies
View Related
Aug 9, 2007
Hello,friends
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.
View 2 Replies
View Related
Dec 11, 2007
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
View 5 Replies
View Related
Aug 22, 2015
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.
View 4 Replies
View Related
May 13, 2015
I have a scenario like below
Product1
Product2 Product3
Product4 Product5
Product1 1
1 0 0
1
Product2 1
1 0 0
1
Product3 0
0 1 1
0
Product4 0
0 1 1
0
Product5 1
1 0 0
1
How to design tables in SQL Server for the above.
View 2 Replies
View Related
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
Feb 13, 2002
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
-------
Fixture
-------
fix_id
fix_date
fix_played
----
Team
----
tem_id
tem_name
-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
View 2 Replies
View Related
Jun 24, 2007
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
View 3 Replies
View Related
Mar 6, 2001
Morning Folks,
I'm setting up SQL Server 7.0 as follows:
12 Gb initial data load;
heavy transaction OLTP;
4 processors;
RAID5
disk space NOT an issue.
I'm trying to figure out what a reasonable initial size for both the Transaction Log and
Tempdb should be. I was thinking 100Mb for each. Is this reasonable, or could either afford
to have more?
Is there a percentage of the .mdf that is recognized as a general rule of thumb for setting up
the Transaction Log and/or Tempdb?
Also, is there any value in assigning Filegroups in a RAID5 setup?
Thanks,
Sergio
View 4 Replies
View Related
May 26, 2007
I've officially done everything. I've uninstalled everything I can think of. I've cleaned the registry until it sparkles. I've removed all directories having anything to do with VS or SQL. I've even run the uninstall tool multiple times and been told everything is OK. However, SQL Server Express STILL tells me there are "beta versions" of stuff on my machine. Do you have any suggestions, or should I just give up in disgust?!?!?!
View 3 Replies
View Related
May 6, 2008
I have a query that searches through a 4 million record table. The data is fed from the UNIX flat file systems so the data is not in optimal search format. So I created some views that massaged the data and then index them. I select and join the original table with the view, with NOEXPAND hint on the view. My question is this theory right: If I put the criteria in the FROM join part then it will make the join easier than if I put it in the where clause?
Example (any difference)
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON m.RecNumber LIKE '112%' AND u.ID = m.ID
versus
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON u.ID = m.ID WHERE m.RecNumber LIKE '112%'
THANKS!!!
View 3 Replies
View Related
Oct 23, 2006
Does the order of columns make a difference in the GROUP BY?Woulde GROUP BY A, B, Creturn different results thanGROUP BY C,A,B ?
View 3 Replies
View Related
Sep 11, 2006
Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.
View 2 Replies
View Related
Aug 10, 2006
Hi,
I have a question i need to ask. Since i'm new to SQL Server, i did a bit of experiment with SQL Server 2005 Express. I made up a simple Access Database file and migrated it to SQL Server. it was success which is good. Now i have a real Access Database file which i built for my client. Since he uses the SQL Server 2000 service pack 4, i was wondering if the version would matter a lot.
Thank you in advance
View 1 Replies
View Related
