Database Design- Referencing Multiple Database
Sep 27, 2007
Hi All,
I am designing database where few of the master tables will reside in different database or in case different server. Scenario is
Server "A" with Database "A" may host the "Accounts" table.
Server "B" with Database "B" may host the "Product" table.
I am designing database "Project" which will hosted in Server "A".
My application requires this master tables [readonly access] as data inserted in my application refers this tables. Also there are reports to be generated which refer this tables.
How do i design my database and sql queries?
I am thinking of approach of having equivalent tables created in my database and writing service which keep tables in my database in sync. This will ensure good perfomance during transaction and reports as they will need to refer this table locally as opposed to different database or different server.
Any thoughts on above approach?? or any better/standard way for such scenarios ?
Thanks in Advance. Your inputs will be of great help.
View 14 Replies
ADVERTISEMENT
Apr 12, 2007
Hello:
My client has a db with the following structure:
Online US Searchable Map of the 50 US States. Users search criteria is the following: Query records by selecting state, county, then record. Each County table has 10-20 tables. All databases combined = 500MB and TLogs = 100MB.
How would you re-design a relational DB where users could query data by state-county-record. Currenty the DB's are created by the County of each state which creates hundreds of DB's in SQLServer with no realtionship to each US state. What would be the best design to ensure good performance, data integrity and maintenance? Would you create 1 DB with all 50 states, create 4 DB's and divide by region(N,S,E,W), 50 DB's of each state or leave it as is with each county it's on DB? Any suggestions would be appreciated.
thx
rob
View 7 Replies
View Related
Jul 3, 1999
Good Morning to Everyone :-)
We're having DB Design discussions in our office for a new project. Basically, we're trying to decide on a DB Solution that allows us to manage multiple online communities. We expect to have several hundred thousand users (about 100k per community), and each user will have associations to user-specific content on the community level.
At some point, we expect to have to export one or several communites into their own Datasource. This will be a downstream event that will not affect us in the immediate future.
In terms of administering the DB and each community, we're having trouble deciding between generating this DB in one inclusive datasource vs creating multiple datasources (one for each community). With that in mind, any advice or suggestions that anyone may have on this subject would be appreciated.
FYI -- We're running CF v4 and MSSQL v7.
Our concerns are as follows:
Data Structure Issues (what abilities/SQL functions will we lose when implementing multiple DB's vs one DB? We definitely have a need to be relational at every level of this application.)
DB Performance (will a one DB solution run more efficiently on MS SQL v7 with high traffic than several DB's?)
Administration (It's our understanding that multiple DB's will cause us to lose our ability to perform table joins, etc. Are there any other shortcomings that we should be aware of?)
Stability Issues (We've heard that one DB vs Multiple DB's will have fewer threads. We've also heard that MS SQL v7 becomes increasingly unstable as more threads get created. Is this true, or is our understanding of this issue wrong?)
CF Integration Issues (How will CF v4 react to handling a large amount of high-traffic DSN's vs. having one DSN and the same amount of traffic?)
Thanks in advance for all your help. We look forward to hearing from you.
Regards,
Abraham Lloyd
Senior Developer
Compbiz.net
View 1 Replies
View Related
Mar 11, 2008
Could anyone provide me an example of the effective way of design a database support multiple languages. Currently I working to design a database for travel website which support 3 different languages, and more languages will be added in in the future. One of my friend advice me to put different languages into the same table for example
Table Hotel
ID
Description_EN
Description_ES
Description_FR
Location_EN
Location_ES
Location_FR
....
But I don't thing it's a good idea to do so since in the future if I would like to add more languages I have to modify the table and I have to replace all of the sql statements. I am seeking for the best solution , could anyone help.
Thank you very much
View 17 Replies
View Related
Sep 16, 2015
I have a single database and 5 user which use this database for DDL and DML operations.
The problem we are facing here is, every time a user need to do some work in this database he/she had to ask(manually) to rest of the 4 users whether they are doing some task over the same DB or not. Sometime due to communication gap one user open any transaction which creates a deadlock for another user to execute any query over the same tables in this DBI want to get rid of this problem by making it configurable from SQL Server part so that if any user which is already accessing this DB, so the other user don't get access to it, Â kind of Mutually exclusive behavior.
How to do this.
View 2 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
Jun 5, 2014
We have a Silverlight based application which currently supports only one production version. Idea is to support three concurrent versions of the same application and user will switch to the newer versions based on their interest or they can still continue with the older version.
We still have to use the existing database for all these three versions.
What is the best way to architect this so that we can differentiate the code between the versions and still keep the data in sync and run all the versions in parallel.
View 7 Replies
View Related
Aug 16, 2006
Hi all,
I wonder if there is anybody that can help with this one.
As you know, you can pass variables into a stored procedure and then use these variables for table names, columns etc.
Now, you also know that you can access another database on the same server just by typing the name of it into your query.
The hard part:
Instead of hardcoding the database name into the stored procedure i want to use a variable and then pass this to reference the database name, in the same way you would reference anything else with a variable. I need to do this as i have to search for various results across multiple databases.
We currently use SQL server 2000 standard, though are looking into SQL 2005 - especially is this problem is easy to resolve in the latter.
Look forward to your help
Regards
Darren
View 6 Replies
View Related
Sep 26, 2006
Hi all, I am still in the process of learning ASP.NET. The simple goal I am trying to accomplish is to set a string equal to a field in my SQL Database using ASP.NET.I am just not sure what the correct syntax is to do so. It may be work noting that I am programming in VB.NET. I have my SQL Data Source setup correctly and inserted into my form with an ID of "PDCalculations".The field that I want to reference is "Percentage" which is an integer field.The reason I want to put the amount into a string is so I can use it in some calculations that are happening on my form.Thanks in advance.
View 1 Replies
View Related
Oct 29, 2015
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies
View Related
Feb 24, 2006
Hello everyone,I have a webcontrol that uses database-structures alot, it uses the system tables in SQL to read column information from tables. To ease the load of the SQL server I have a property that stores this information in a cache and everything works fine.I am doing some research to find if there are anyway to get information from the SQL server that the structure from a table has changed.I want to know if a column or table has changed any values, like datatype, name, properties, etc.Any suggestions out there ?!
View 3 Replies
View Related
Jul 23, 2005
I have a system that basically stores a database within a database (I'msure lots have you have done this before in some form or another).At the end of the day, I'm storing the actual data generically in acolumn of type nvarchar(4000), but I want to add support for unlimitedtext. I want to do this in a smart fashion. Right now I am leaningtowards putting 2 nullable Value fields:ValueLong ntext nullableValueShort nvarchar(4000) nullableand dynamically storing the info in one or the other depending on thesize. ASP.NET does this exact very thing in it's Session State model;look at the ASPStateTempSessions table. This table has both aSessionItemShort of type varbinary (7000) and a SessionItemLong of typeImage.My question is, is it better to user varbinary (7000) and Image? I'mthinking maybe I should go down this path, simply because ASP.NET does,but I don't really know why. Does anyone know what would be the benifitof using varbinary and Image datatypes? If it's just to allow saving ofbinary data, then I don't really need that right now (and I don't thinkASP.NET does either). Are there any other reasons?thanks,dave
View 7 Replies
View Related
Aug 16, 2007
Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas
View 3 Replies
View Related
Oct 12, 2012
I ran into an interesting situation. I'm working on contract and was looking at creating an ERD for an existing database when I ran into a problem. I found FK's that are referencing columns that do not have a unique constraint or a unique index.Â
I don't know the history of the database but was there a time in SQL Server history where this would have been possible? I scripted out the tables and created it in a test database. When I run the script to create the FK I get the following message.Â
I double checked the original tables and this FK does exist in table1 and there is no unique anything in the referenced table, table2. Currently the database is running on SQL Server 2008 Ent.
ALTER TABLE [dbo].[table1] WITH CHECK ADD CONSTRAINT [FK_table1] FOREIGN KEY([Col1])
REFERENCES [dbo].[table2] ([col2])
There are no primary or candidate keys in the referenced table 'dbo.table2' that match the referencing column list in the foreign key 'FK_table1'.
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
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
Feb 12, 2008
Using SQL Server Express 2005, I have two databases. AppDB - The main application database.GeoDB - A somewhat static ZIP code / states / other geographic stuff databaseI need to have some foreign key columns in tables in AppDB reference columns in the GeoDB database tables. Eventually other application database besides AppDB will be doing the same thing in our infrastructure. After googling and reading for days, here is what I
think I know:You cannot create foreign keys that reference tables in another database in SQL Server.You
cannot create foreign keys that reference columns in a view, and you definitely cannot make an index on a view that has base tables in another database.You can create a trigger that references tables in another database, but this can be flaky? (nested/recursive trigger problem).SQLServer
2005 supports multiple schemas within the same database. Maybe I should logically separate my databases this way? Seems like it would be a tough solution to manage since I already have some databases live in production that will eventually use this 'static' GeoDB. Also, seems like it
wouldn't be as portable as keeping the GeoDB info in its own database,
but maybe I'm being too software engineer-ish here - afraid of low
cohesion, high coupling.I will greatly appreciate any advice I can get, or any more options I am missing. Thanks,Adam Nofsingerucnmedia.com
View 2 Replies
View Related
Jun 7, 2006
Hi
This is for Sql Server 2000.
I get the following error :
View or function 'PS_EMPLOYMENT' is not updatable because the modification affects multiple base tables.
Sql is as follows:
INSERT INTO PS_EMPLOYMENT(EMPLID,EMPL_RCD,PER_ORG,BENEFIT_RCD_NBR,HOME_HOST_CLASS,OWN_5PERCENT_CO,
SECURITY_CLEARANCE,NEE_PROVIDER_ID,POSITION_PHONE,BUSINESS_TITLE,REPORTS_TO,
SUPERVISOR_ID)
VALUES('000004', 0, ' ', 0, 'M', 'N', ' ', ' ', ' ', ' ', ' ', ' ')
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Searching the KB i only found links to SP1 and SP3, the server is already at SP3.
BOL says the following:
SQL Server 6.x :
Updatable views were restricted to modifications that affected only one table
SQL Server 2000:
Updatable views can modify more than one table involved in the view. The DELETE, INSERT, and UPDATE statements can reference a view as long as SQL Server can translate the user's update request unambiguously to updates in the base tables referenced in the view's definition.
So why can this view not be updated in SS2000 SP3 ?
Here is a copy of the view defintion:
ALTER VIEW PS_EMPLOYMENT
(EMPLID, EMPL_RCD, PER_ORG, BENEFIT_RCD_NBR, HOME_HOST_CLASS, LAST_DATE_WORKED, HIRE_DT, REHIRE_DT,
TERMINATION_DT, SENIORITY_PAY_DT, CMPNY_SENIORITY_DT, SERVICE_DT, PROF_EXPERIENCE_DT,
LAST_VERIFICATN_DT, EXPECTED_RETURN_DT, LAST_INCREASE_DT, OWN_5PERCENT_CO, PROBATION_DT,
SECURITY_CLEARANCE, NEE_PROVIDER_ID, POSITION_PHONE, BUSINESS_TITLE, REPORTS_TO, SUPERVISOR_ID)
AS
SELECT A.EMPLID ,A.EMPL_RCD ,A.PER_ORG ,A.BENEFIT_RCD_NBR ,A.HOME_HOST_CLASS ,D.LAST_DATE_WORKED ,
D.HIRE_DT ,D.LAST_HIRE_DT ,D.TERMINATION_DT ,A.SENIORITY_PAY_DT ,A.CMPNY_SENIORITY_DT ,A.SERVICE_DT ,
A.PROF_EXPERIENCE_DT ,A.LAST_VERIFICATN_DT ,D.EXPECTED_RETURN_DT ,A.LAST_INCREASE_DT ,A.OWN_5PERCENT_CO ,
A.PROBATION_DT ,A.SECURITY_CLEARANCE ,C.NEE_PROVIDER_ID ,A.POSITION_PHONE ,A.BUSINESS_TITLE ,D.REPORTS_TO ,
D.SUPERVISOR_ID
FROM PS_PER_ORG_ASGN A ,PS_PER_ORG_INST C , PS_JOB D
WHERE A.EMPLID = C.EMPLID AND A.ORG_INSTANCE_ERN = C.ORG_INSTANCE_ERN AND A.EMPLID = D.EMPLID AND A.EMPL_RCD = D.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB2 WHERE D.EMPLID = JOB2.EMPLID AND D.EMPL_RCD = JOB2.EMPL_RCD AND (( JOB2.EFFDT <= { FN CURDATE() }) OR (JOB2.EFFDT > { FN CURDATE() } AND { FN CURDATE() } < ( SELECT MIN(J2.EFFDT) FROM PS_JOB J2 WHERE J2.EMPLID = D.EMPLID AND J2.EMPL_RCD = D.EMPL_RCD) ) )) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.EMPL_RCD = D.EMPL_RCD AND JOB3.EFFDT = D.EFFDT )
View 3 Replies
View Related
Jun 15, 2006
For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID . . . . . . . . . . int
Name . . . . . . . . . . . nvarchar(50)
ManagerID . . . . . . . . . . . int
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?
View 6 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
