Database Design Question - Isolated, Unrelated Tables
Jun 25, 2007
Hi,
I have a question regarding best practices in database design. In a
relational database, is it wise/necessary to sometimes create tables
that are not related to other tables through a foreign Key
relationship or does this always indicate some sort of underlying
design flaw. Something that requires a re evaluation of the problem
domain?
The reason I ask is because in our application, the user can perform x
number of high level operations (creating/updating projects, creating/
answering surveys etc. etc.). Different users can perform different
operations and each operation can manipulate one or more table. This
part of the system is done and working. Now there is a requirement to
have some sort of audit logging inside the database (separate from the
text based log file that the application generates anyway). This
"audit logging" table will contain high level events that occur inside
the application (which may or may not relate to a particular
operation). This table is in some sense related to every other table
in the database, as well as data that is not in the database itself
(exceptions, external events etc.). For example : it might have
entries that specify that at time x user created project y, at time A
user filled out survey B, at time C LDAP server was down, At time D an
unauthorized login attempt occurred etc.
As I said, these seems to suggest a stand alone, floating table with a
few fields that store entries regarding whats going on the system
without any direct relationship to other tables in the database. But I
just feel uneasy about creating such an isolated table. Another option
is to store the "logging" information in another schema/database, but
that doubles the maintainance work load. Not really looking forward to
maintaining/designing two different schemas.
I had a look at the microsoft adventureworks database schema diagram
and they seem to have 3 standalong tables - AWBuildVersion, ErrorLog
and DatabaseLog (unless i am reading it wrong!)
Any advice, Information or resources are much appreciated.
View 12 Replies
ADVERTISEMENT
Apr 23, 2006
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
Thank you,
Lisa
View 2 Replies
View Related
Mar 7, 2006
We're getting duplicate records b/c we're joining two tables that really don't have a unique common field. Does anyone know of a way to join two tables that aren't completely related?? I know this is contradictory to the entire concept of a relational db :).
View 4 Replies
View Related
Oct 26, 2004
Hi
I have similar problem and i explained in detail here http://www.balainfo.com/forums/viewtopic.php?t=39
Pls help me to fix this sql
Regds
Bala
View 1 Replies
View Related
Sep 8, 2006
Hi,
I have two tbls (trade & retail) - at the minute I have a query (in just one tbl) which looks like this
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded') AND (DATEDIFF(d, OrderDate,
@StartDate) <= 0) AND (DATEDIFF(d, OrderDate, @EndDate) >= 0)
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
I then have the same query (bar a couple of differences) for the trade tbl which looks like this:
SELECT SUM(total) AS WeekTradeTotal, COUNT(*) AS MonthTradeOrderNo, DATEPART(wk, order_date) AS SalesWeek, YEAR(order_date) AS SalesYear
FROM dbo.Orders_Trade
WHERE (order_status <> 'Deleted') AND (account = @Account) AND (order_status <> 'Removed') AND (order_status <> 'Refunded') AND
(TradeCustomerID <> 'Cha928') AND (DATEDIFF(d, order_date, @StartDate) <= 0) AND (DATEDIFF(d, order_date, @EndDate) >= 0)
GROUP BY YEAR(order_date), DATEPART(wk, order_date)
ORDER BY YEAR(order_date), DATEPART(wk, order_date)
Now what I want to do is make one query which has the same WHERE expression(s) but for both tbls at once.
Because the two tbls have a couple of different names in their fields (I.e the retail tbl identifier is called CustomerID and the Trade tbl identifier is called TradeCustomerID). I thought the best bet would be to first dump the data into a temp tbl giving new fields names so that they all match, and then run the query.
But I haven't got a clue how to write a temp tbl SP, nor how to clean it up afterwards as we are talking a whole lot of data...
Any help anyone?
View 6 Replies
View Related
Apr 23, 2008
Hello,
I'm struggling with a query that needs to use Not Exists between two unrelated tables. I'm not sure of the best way to handle this with SQL Query Analyzer.
I have three tables, employees, courses and training_records. There is no link between employees and courses other than through the training records table. My problem is that I am unsure of how to pull all employees and an associated course id and course title when there is not a training record for that employee/course?
I had a query that would work when I specified the course id, but I wanted to do this for a list of about 25 courses.
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName
FROM empdata e
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
LEFT JOIN #TrainingCourses tc
ON tc.ClassID = tr.classid
WHERE tc.ClassID = 1377
AND tr.employee = e.empnumber)
I then modified it to get the classid and name
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName
FROM empdata e
LEFT JOIN #TrainingCourses tc
ON tc.ClassID = 1377
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
WHERE tr.ClassID = tc.ClassID
AND tr.employee = e.empnumber)
I then got it to work using the full list
SELECT e.empnumber, e.Nickname + ' ' + e.lastname as employeeName, tc.ClassID, tc.ClassName
FROM empdata e
LEFT JOIN #TrainingCourses tc
ON tc.ClassID in (1032, 1054, 1059, 1060, 1062, 1063, 1069, 1072, 1074, 1075, 1122, 1189, 1190, 1191, 1192, 1193, 1210, 1218, 1219, 1220, 1310, 1377, 1411)
WHERE NOT EXISTS (SELECT * FROM #TrainingRecords tr
WHERE tr.ClassID = tc.ClassID
AND tr.employee = e.empnumber)
My results seem to work like I want them to, but I have never joined a table without actually joining on a value from each table. In this case I join to a list of values and that just looks odd to me. Is this good or crappy development? Is there a better way this should be done?
View 6 Replies
View Related
Sep 16, 2015
I have a several questions with regards to setting up a server to cater for multi tenant database.
I have a requirement to host 100 isolated databases for the purpose of multi user environment where each user has it's own database. This is due to security restrictions that why it was requested to be such.
1. What I'm recommending is to setup servers with 256GB of RAM to host multiple databases. Are there servers which can accomodate 256GB? Is this optimal with regards to performance? Or should I break it down to smaller servers with less RAM?
2. Is there a way to determine the CPU requirements for the DB server, taking for example we have 20 databases per DB instance? Are there any best practices / calculators available?
3. Additionally, data from the 100 DB's needs to be consolidated into 1 central repository for Analytics purposes. What I'm looking at is to utilize SQL Server transaction replication. Is there any drawback with this approach? Are there any other approach we could use to easily replicate data across 100 database back to 1 central repository?
View 0 Replies
View Related
Jul 20, 2005
Dear sirI have one SQL Server database.In this database more than 20 tables existsthat their structures is same.I have two way for design this tables:1.Create all tables separately(create more than 20 tables)2.Create one table and separate each groupby type(add column type to table and assign samevalue for each group)What is better solution?Please help me.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Sep 19, 2000
I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?
Sample Structure
table = Claim
Field 1 = ClaimTypeA_ID
Field 2 = ClaimTypeB_ID
Field 3 = ClaimTypeC_ID
Field 4 = ClaimTypeD_ID
Field 5 = ClaimTypeE_ID
Field 6 = ClaimTypeF_ID
The six field relate to the 6 different tables ID.
If I do this how do I store the data? put 0's in each of the claim types that are not used???
Any suggestions would be appreciated.
View 2 Replies
View Related
Jul 22, 2015
How to Change Order of Column In Database Tables
View 10 Replies
View Related
Feb 2, 2007
I'm a bit confused about how ssis handles concurrent package runs
let's say I'm running this package and I 've got 3 variables set in it
VARA
VARB
VARC
and by default they are all set to 0
if I run
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARA].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARB].Value;1
dtexec /File "C:ControlRoom.dtsx" /SET PackageVersion_Builder.Variables[VARC].Value;1
I'm expecting to run 3 isloated version of the package with in
first version
VARA=1
VARB=0
VARC=0
second version
VARA=0
VARB=1
VARC=0
third version
VARA=0
VARB=0
VARC=1
but it doesn't seem like doing that the maxconcurrent variable is set to 40 to be on the safe side.
when I run I get
first version
VARA=1
VARB=0
VARC=0
second version
VARA=1
VARB=1
VARC=0
third version
VARA=0
VARB=1
VARC=1
any ideas?
Thanks
View 2 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
Jul 28, 2015
what are some common techniques for ensuring an isolated temp table scope? For example, if 2 different sprocs happen to crud a temp table with the same name? I'm guessing that big SQL shops establish a standard for this early on to avoid conflicts between sprocs.
View 3 Replies
View Related
Sep 6, 2005
Hi all,I'm regularly getting the "deadlocked..you're the victim" message when twothreads work on a table at the same time via JDBC. The two threads don'tupdate the same records. I suspect the cause is related to index or pagelocks, and/or the fact that the UPDATE statements are doing table scansbecause there's no index on the primary key (no, I'm not the DBA!)1. Thread A selects some records to play with:select top 50I.*,M.this,F.thatfrom APInvoice Iinner join M ...inner join F ...where I.CmStatus = 'O'(There is an index on CmStatus + another column called CmTime, so I expectthis index to be used. It's also the only index on the table.)2. Gradually, within the same transaction that did the SELECT, thread Aupdates all 50 selected records:update APInvoiceset CmStatus = 'S',CmTime = getdate()where itemid = (an ID number from the select in step#1).(There is no index on itemid, which is the unique primary key column)(There is an index on CmStatus + CmTime).3. While step#2 is running, thread B starts and does a single update.That's all that thread B does:update APInvoiceset CmStatus = 'C',CmTime = getdate()where itemid = (an ID number that is NOT in the list of records being usedby thread A)I consistently get deadlocks whenever thread A & B run at the same time.To resolve ths problem, I'm looking at the following actions:1. Create a unique primary index on ITEMID.2. Add an "(UPDLOCK)" or "FOR UPDATE" to the SELECT statement.Are these reasonable things to try? Is there something else I should bedoing?
View 1 Replies
View Related
Jul 20, 2005
I would like to retrieve 10(dynamic) records of table x (proucts) foreach user in table y (users). Can this be done?I would like the end result to be something like this: (would this bea union?)__________________________y.name | x.pid | x.pnameBob | 1 | forkBob | 2 | spoon.... | |Bob | 10 | potatoJeff | 11 | penetc.....__________________________But also with the number to return based off of a query, ex-select @pcount = count(products)select @ucount = count(users)select @pcount / @ucount10And lump all this in an Stored procedureex-get number of total records in x, divide by total y = zselect z records for each user in y.You would be a master in my book if you can give me hints on this one!Thanks,Jeff
View 1 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
Mar 12, 2015
I've built a robust looking Calendar dimension but that's easy because its all so well structured. Every date belongs to a particular week and every week belongs to a particular month and every month belongs to a particular quarter etc. January 1st is always going to be a member of week 1, month 1, quarter 1.But here's the rub, real life isn't as predictable and stable as a calendar.
Lets say you have an table containing personal information. You might have gender and marital status in there and even though they both relate to one person, they're unrelated to each other so how do you put those in a hierarchy? Which one goes first and which second because you can get combinations of either.
View 1 Replies
View Related
Nov 4, 2015
I tried using the page below but wasn't able to quite properly adapt it to my scenario
[URL]
I have two UNRELATED (can't be related for reasons outside the scope of this, other relationships already exist) tables.
Each table does, however, contain a WorkerID:
WorkerTimesheets:
WorkerID, HoursLogged, Date
3,10,2015-05-05
3,6,2015-05-10
4,8,2015-05-01
Then the unrelated table "ConstructionSites"
SiteID, SiteManagerID, SiteOpen,SiteClosed
A5, 3,2015-01-01,BLANK
What I want to do is be able to show a measure that SUMS the number of hours logged by anyone who is a SiteManager from the ConstructionSites table.
I wanted to do a SUMX of WorkerTimesheets against HoursLogged, but FILTER against WorkerTimesheets[WorkerID] = ConstructionSites[SiteManagerID] so only workers who are also SiteManager would be counted.However, I can't seem to get that to resolve it always throws an error along the lines that it can't determine context.
View 3 Replies
View Related
Jul 11, 2013
how to set 'Ignore Unrelated Dimensions' property in Tabular Model.
View 3 Replies
View Related
May 13, 2007
I used to get flat files which i need to import into tables..
The flat files data which contains.....
How should i design the tables so that i can get the output in single row...
Table1ForRow1
--------------
col1,100
col2,1
col3,xx
col4,yy
col5,,,
col6,20030101
Table1ForRow2
-------------
col1,100
col2,2
col3,20030101
Table1ForRow3
------------
col1,100
col2,3
col3,01
col4,20030101
FlatFiledata:
------------
100,1,xx,yy,,20030101
100,2,20030101
100,3,01,20030101
I want the output:
100,1,xx,yy,20030101,2,3,01
View 1 Replies
View Related
Feb 5, 2005
I'm grappling with this design problem right now:
I have a table of users. Every user has an e-mail address and (hashed) password. Some of those users work for a company, and some of them do not. Of those who do not work for a company, some are salespeople who sell to one or more companies. Some users are simply administrators who don't work for a specific company. So here's what my users table looks like right now: "UserID, Email, Password, CompanyID (Nullable), IsAdmin"
And here's my companies table: "CompanyID, CompanyName, SalespersonID"
Of course, I could separate it out and make a Users table, an Employees table, and a Salespeople table. The way the relationship works out, though, I could use the same ID number for all three tables, and that indicates to me that perhaps they all belong in the same table. It seems silly, after all to have a Salespeople table whose only field is "UserID."
Two factors of the first design concern me: First is the fact that a salesperson could also have a company. I guess I could write a check constraint to prevent this, but doesn't having the companyID in the Users table violate a normalization rule? Maybe? The second is the fact that the Companies table relies upon Users, which in turn relies upon Companies. In OOP, this usually isn't a good thing, but I'm not sure whether it's cause for concern in a relational database.
Anyway, I really don't know what I should be doing with this design. Any suggestions?
Thanks in advance,
-Starwiz
View 1 Replies
View Related
Jul 23, 2005
I have an applicaton in which I collect data for different parametersfor a set of devices. The data are entered into a single table, eachset of name, value pairs time-stamped and associated with a device.The definition of the table is as follows:CREATE TABLE devicedata(device_idintNOT NULL REFERENCES devices(id),-- id in the devicetabledatetimedatetimePRIMARY KEY CLUSTERED,-- date creatednamenvarchar(256)NOT NULL,-- name of the attributevaluesql_variantNOT NULL-- value)For example, I have 3 devices, and each is monitored for two attributes-- temperature and pressure. Data for these are gathered at say every20 minute and every 15 minute intervals.The table is filled with records over a period of time, and I canperform a variety of SQL queries.I have another requirement which requires me to retrieve the *latest*values of temperature and pressure for each device.Ideally, I'd like to use the data I have collected to get thisinformation, and I suppose I can.What I need is the SELECT statement to do this.I'd appreciate it very much, if someone can help provide that.Conceivably, I could use a SQL server View for making this easier forsome of my users.One alternate technique I thought was to create another table which I*update* with the latest value, each time I *insert* into the abovetable. But it seems like a waste to do so, and introduces needlessreferential integrity issues (minor). Maybe for fast access, that isthe best thing to do.I have requirements to maintain this data for several months/year ortwo, so I am dealing with a large number of samples.Any help would be appreciated.(I apologize if this post appears twice)
View 9 Replies
View Related
Dec 28, 2005
I am developing a Content management system of sorts and I want it
to be pretty flexible. I have noticed that with web content (News,
Articles, Events, FAQs) that there is a lot of similarity between these
items. They all have the same basic fields:
TitleSynopsisBodyStartDateEndDate
(for News, Articles, FAQs start and end date can be used for content
expiration. For events, they are used for the actual event dates.)
But for some types of content, I will need fields specific to that
type. For instance, I want to have a few custom settings for a "Photo
Gallery" type, like, how many rows/columns of thumbnails to display per
page.
I feel like I have 3 options, but would really appreciate your advice. I created diagrams for the 3 options, located here: http://nontalk.com/dbdesign/
View 2 Replies
View Related
Jan 18, 2008
I'm designing a database with 3 tables called Function, Test andScene.A Function has multiple Tests, but a Test has only one Function. Amany to many relationship exists between Test and Scene therefore Ineed a junction table between these two tables - giving 4 tables intotal. The Test table would store a foreign key, the primary key ofthe Function table.There is a problem with design though and that is that Functions andScenes are actually defined before the Test is defined. Therefore itshould be possible to create a Function and add to id its Scenes,before Tests have been defined. In other words, Scenes are as much apart of a Function as they are of Tests. Tests are in fact only ofrelavence to testers. Anyway, to satisfy this scenario, a Junction boxis also needed beween Function and Scene. This creates a loop betweenall tables.Is this a good approach? Any other suggestions or advice on thematter? Any advice regarding data integrity?Thanks,Barry
View 1 Replies
View Related
Jul 20, 2005
i am a beginner of database design, could anyone please help me tofigure out how to make these two tables work.1) a "players" table, with columns "name", "age"2) a "teams" table, which can have one OR two player(s)a team also has a column "level", which may have values "A", "B",or "C"how do you build the "teams" table (the critical question is "do ineed to create two fields" for the maximum two possible players?")how do you use one query to display the information with the followingcolumns:"name", "age", "levelA", "levelB", "levelC" (the later three columnsare integer type, showing how many teams with coresponding level thisplayer is in).now suppose i don't have any access to sql server, i save the datainto xml, and load it into a dataset. how could you do the selectionwithin the dataset? or ahead of that, how do you specify the relationsbetween "players" and "teams".the following is the schema file i am trying to make (I still don'tknow if i need to specified the primary key... and how to buildrelation between them):<code><?xml version="1.0" ?><xs:schema id="AllTables" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="AllTables" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Players"><xs:complexType><xs:sequence><xs:element name="PlayerID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Name" type="xs:string" minOccurs="0" /><xs:element name="Age" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element><xs:element name="Teams"><xs:complexType><xs:sequence><xs:element name="TeamID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Level" type="xs:string" minOccurs="0" /><xs:element name="Player1" type="xs:int" minOccurs="0" /><xs:element name="Player2" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></code>
View 2 Replies
View Related
Feb 28, 2008
Hi,
Currently i am developing a job portal in ASP 2.0, SQL Server 2005 which involves Job Seeker registration, Searching of resumes, applying for job Posting, Employer Registration, Create Job Posting, Searching for Job Seeker etc.
The Job Seeker is allowed to upload a word document of size up to 500Kb which is stored in Table as varbinary.
Right now I have MemberShip/Roles in seperate database. The Job Portal Tables are in seperate Database. I was told to split the Tables so that Tables of JobSeeker are One database and Employer to another Database so that they speed up the performance.
I have several tables that bridge (thats either store id's of Job Seeker or Employer) like Job Postings applied, Saved Postings Job Seeker, Job Postings of the Employer, Job Posting (Applied ones) alert etc.
Can any give me how to create a good Database design (one or more) with excellent performation. Right now I have one Database for Job Portal related tables excluding membership. The mapping of key fields including the fields that are enabled for Text indexing are given below.
(JobSeekerTable - Stores Personal Details)
JobSeekerId (PK)
...............
(JobSeekerResumeTable - Stores Resume Details)
JobSeekerResumeId (PK)
JobSeekerId (FK)
Job Title (enabled Text Indexing)
........
(JobSeekerDocTable - Stores Resume Details)
JobSeekerDocId (PK)
JobSeekerId (FK)
Resume (as varbinary) (enabled Text Indexing)
Covering Letter (Text)
........
(JobSeekerPostingTable - Stores Job Postings Saved by the Job Seeker)
JobSeekerPostingId(PK)
JobSeekerId (FK)
JobPostingId (FK)
......
(JobSeekerAppliedTable - Stores Job Postings Applied by the Job Seeker)
JobSeekerAppliedId(PK)
JobSeekerId (FK)
JobPostingId (FK)
.....
(CompanyTable - Employer Details)
CompanyId(PK)
.....
(JobPostingTable - Stores the information of the Job Posting created by Employer)
JobPostingId(PK)
CompanyId(FK)
Job Title (enabled Text Indexing)
Job Desc(enabled Text Indexing)
.....
(JobPostingConTable - Stores the information of the Job Posting Location Details )
JobPostingConId(PK)
JobPostingId(FK)
.....
(CompResumeSaved - Job Seeker details saved by Employer)
CompResumeSaved(PK)
CompanyId(FK)
JobSeekerId(FK)
.....
Eventually more tables would be added. Can any one tell me how to speed up the performance (particulary search engine fo Employer for searching resumes & Jobseeker for searching job Postings.) I hope I have mentioned everything clearly.
Thanks,
Uma Ramiya
View 4 Replies
View Related
Jun 10, 2015
I have 3 tables (accnt, jobcost, and servic15). all with the same fields (code, jno, ven, date). I need to insert the data from these tables into another table called dummy with the same fields, in one statement or query.
View 3 Replies
View Related
Jan 4, 2006
I have a general SQL design-type question.
I want to log errors to a table. If the error is with a URL, I want to store the URL. These URLs can be very large, hundreds of characters, but I only need to store it if it causes the error, which should be very infrequent. Which is the better design:
Create a large varchar field in the log table to hold the URL, or null if the error wasn't with the URL.
Create a foreign key field in the log table to a second URL table, which has a unique ID and a large varchar, and only create a record in this table if the error is with the URL.
One concern I have with design 2 is that there could be many other fields that are infrequent. Do I create a separate table for every one?
Richard
View 3 Replies
View Related
May 28, 2015
I have below DB structure in MSSQL for a small application which follow relational approach. Data retrieval (for Hostels) will need several Join, may be Key-Value approach where data retrieval will be fast.
Hostels
------------
HostelId,
Name,
Address,
CategotyId,
SubCategoryId,
FoodCategoryId,
LandLordId
Data:
1 H1 Address1 1 1 2 20
2 H2 Address2 1 2 2 21
3 H3 Address3 2 2 1 17
Category
----------
CategoryId,
CategoryName
[code]...
View 10 Replies
View Related
Oct 20, 2006
Hi,
I have a problem in design the tables. My main task is to learn how to give the Match Score.
I have hundreds of dataset and one of them is like this:
Test Record Number: 19
Prospect ID = 254040088233400441105260031881009
Match Score = 95
Input Record Fielding ( eg wordnumber[Field] ) : 1[1] 2[1] 3[11] 4[11] 5[11]
Prospect Word = 1 type = 1 match level = 4 input word = 1 input type = 1
Prospect Word = 2 type = 2 match level = 0 input word = NA input type = NA
Prospect Word = 3 type = 3 match level = 4 input word = 2 input type = 1
Prospect Word = 4 type = 11 match level = 4 input word = 3 input type = 11
Prospect Word = 5 type = 13 match level = 4 input word = 4 input type = 11
Prospect Word = 6 type = 14 match level = 4 input word = 5 input type = 11
Now I have all my data stored in the DB and I seperated them into 3 tables and their structures are:
1) prospect (id, testrecordnumber, prospectID, matchscore)
2) inputfieldind (id, prspid, inputword, inputfield)
3) prospectinfo (id, prspid, prospectword, prospecttype, matchlevel, inputword, inputtype)
and the prspid in table 2 & 3 refers to the prospectID in table 1.What I did was setting:
a) prospect table as case table with id AS key, prospectID AS input & predictable;
b) and the other two as nested tables with inputword/inputfield AS key & input, prospectword/prospecttype/matchlevel/inputword/inputtype AS key & input .
But it shows error for having multiply key columns...
And also I am thinking about using the Naive bayes algorithm. Can I also have some suggestion on this?
Thanks
View 3 Replies
View Related
Sep 9, 2015
In the Operating environment databases, may be made tables in the database on a temporary basis but they are still yet and they are not removed, how to identify tables that have been made on a temporary basis are not used (don’t have any read & write records)?
View 2 Replies
View Related
Jun 17, 2015
I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
accnt acc1167
ven1 amnt1 ven job# amnt
1167 100 1167 1 200
1152 50 1167 2 300
1167 110 1167 3 100
1167 300 1167 4 200
1252 1050 1167 5 200
1167 210 1167 6 150
1167 1150
1167 130
2113 800
1167 550
1167 1200
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
acc1167
ven job# amnt
1167 1 61.4
1167 2 92.1
1167 3 30.7
1167 4 61.4
1167 5 61.4
1167 6 46.05
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
UPDATE acc1167
sum1 = sum amnt1 where ven1 = '1167'
from accnt
sum2 = sum amnt
from accnt
SET amnt = sum2/sum1*amnt
FROM acc1167
View 2 Replies
View Related
May 20, 2015
I normalized the below tables but I am finding it difficult to copy data to the new tables. How do I copy data from existing table to the normalized tables? see the table structure below and other supporting information:
SKU_DATA(SKU,SKU_Description,Department,Buyer) Note: this table already has data in it.
CREATE TABLE SKU_DATA (
SKU
Integer NOT
NULL,
[code].....
The table structure above have two three determinants( SKU,SKU_Description and Buyer). SKU and SKU_Description are candidate keys. Primary key is SKU.
Normalization : SKU_DATA(SKU,SKU_Description, Buyer)
BUYER(Buyer,Department)
View 2 Replies
View Related
