Generic Database Design
Oct 12, 2007
Hello,
Can someone please guide me to online resources or books on how to design generic database? I mean, I can have a "Record" that can have a set of fixed fields like: ID, Title, CreatedOn, etc ... and I can add as many properties I want to that record in a vertical way.
Then based on the generic tables present, I can fit in the same table, a Record for Customer, a Record for House, a Record for Order. All share the same set of Fields, but each has its own set of proeprties.
This way, I design my database once, and customize it to fit any type of applications I need.
Is there something like that or just dreaming?
thanks
View 6 Replies
ADVERTISEMENT
Jan 6, 2006
I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.
1. Have a generic table which would have table name and description and whatever else that was required.
2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.
This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.
Not sure if I am making sense but I hope someone understands the attempt.
thanks
View 10 Replies
View Related
Nov 22, 2005
Hi,
My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..
But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.
In mysql, I accomplish this using
dbfield+interval '$arg' minute between date1 and date1
But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?
My frontend scripting language is php
Thanks d'advance
Celia
View 1 Replies
View Related
Jul 23, 2007
Hello,
I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?
View 8 Replies
View Related
Jul 20, 2005
Greetings to all database professionals and laymen,Let us make a bold assumption that we have developed a softwaretool for the SQL Server environment which simply acts as an interfacebetween an end-user in an organization and the database, throughthe exclusive use of stored procedures which are authored by theorganization or by software developers.All development work at the application software level may therebybe conducted within SQL, by the development of TSQL storedprocedures and their coordination across an organization.The question then needs to be asked what are the advantages of thisarrangement and what are the disadvantages. I would appreciateyour comments here, as it is difficult for folk heavily involved (eg: me)in something to obtain objective opinion otherwise.Potentially it is possible to construct an entire database applicationsoftware package using only TSQL stored procedures and thistool. When a database backup is conducted not only the data isbacked up but also all the "program development" suite, in theform of stored procedures (and their scheduling, for example).One of the advantages from my perspective is that this arrangementimplies the possibility that all software external to the database maybe made redundant (except this tool), and along with it, all the otherredundancies of managing application development (life-cycles) withinthe database and then coordinating these changes with softwareexternal the database (particulary loading it to the client environment)You see, I believe that (for example) any and every application writtenin VB, C, etc, etc, etc (external to rdbms) expressly for database software(ie: rdbms software such as SQL Server) involves a redundancy of datadefinitions required ---- once within the VB and once within the db.I would appreciate any feedback on any of the above issues, andwish everyone the compliments of the season.Pete BrownFalls CreekNSWOz~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~EDITOR:BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}Thematic Threading: Publications of Peace and Of Great SoulsWebulous Coordinates: http://www.mountainman.com.auQuoteForTheDay:"Consciousness is never experienced in the plural, only inthe singular. How does the idea of plurality (emphaticallyopposed by the Upanishad writers arise at all? .... theonly possible alternative is simply to keep the immediateexperience that consciousness is a singular of which theplural is unkown; that there *is* only one thing and thatwhat seems to be a plurality is merely a series of differentaspects of this one thing produced by deception (the Indianmaya) - in much the same way Gaurisankar and Mt Everest turnout to be the same peak seen from different valleys."- E. Schrodinger, "What is Life"--------------------------------------------------------------------
View 18 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
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
Nov 14, 2000
In general (I know.. lots of factors) but in general will adding more RAM to a SQL box help the performance? I'm working w/ 256MB right now, and deciding whether to add 256MB or 512MB? Any advice
Thanks and sorry so generic but this is a vendored app and they know of design issues but for now that can't be changed.
View 1 Replies
View Related
May 26, 2006
Hi guys
I have a simple problem:
I want to be able to query with a start date(@start) and then calculate 1 year from there. I figured since the date is of type integer just add 364 and it will give me the day before 1 year.
So if its 1998-05-01, Id get 1999-04-31.
Code:
DECLARE @FinStart DATETIME
DECLARE @FinEndDATETIME
SET @FinStart = '1998-05-01' --is of type int .:. +365 is adding 1 year
SET @FinEnd = @FinStart + 364
SELECT @FinStart
SELECT @FinEnd
Problem, this doesnt cater for leap years.
Any suggestions?
The help is always appreciated!
Justin
View 2 Replies
View Related
Apr 11, 2008
Here's the scenario:
whenever a change (insert/update/delete) has occured in any of the tables, it needs to be logged by adding the changed table's name, and changed columns' names (if any) into the special ChangeLog table.
So, is it possible for me to write a generic trigger that would get the name of the table it just executed upon and names of every column affected by the UPDATE and then insert those into the Log table?
If this is not possible, how do you suggest it should be done?
I'm familiar with standard SQL but T-SQL is completely new to me, so please bear with me if my question makes no sense...
Thanks!
View 1 Replies
View Related
Dec 21, 2006
Hallo all :
How to disappear the generic bar in the report, or to disappear some options (example export). I speak about the bar which in the top of the report or there are the numbers of pages.
Thank's.
View 4 Replies
View Related
Apr 28, 2007
Hello,I have a DataSet which results from accessing an SQL database.Each row is an Article and has various columns like Title, Description, etc.I have created a Generic List as follows:Dim Articles As Generic.List(Of Article)Article is a class with various properties. Each property is the same as each column in the dataset.I want to fill the Generic List with the data from my DataSet.How can I do this?Thanks,Miguel
View 1 Replies
View Related
Jun 5, 2004
I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.
I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.
For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @tablename, @code, @desc, and then create a new record in the table specified by @tablename.
I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.
Any help would be greatly appreciated!
View 10 Replies
View Related
May 11, 1999
I am attempting to create a generic stored procedure in SQL Server 6.5 which returns a record count of a specific table passed in as a parameter. The stored procedure looks like this"
CREATE PROCEDURE asp_GetRecordCount
@tablename varchar(30), @recordcount integer OUTPUT
AS
/* Generic stored procedure which returns
number of records in a table.
*/
SELECT @recordcount = COUNT(*) FROM @tablename
RETURN
GO
The problem is that when I save the stored procedure, SQL Server balks at the FROM @tablename entry. Any suggestions? Thanks.
View 3 Replies
View Related
May 17, 2006
I have a bunch of tables for my basketball club's d/b. I want to be able to interrogate them to build a web page showing player stats. For example, I want a table showing the top 5 scorers, top 5 3-point scorers, etc. What's got me foxed is how to join it all up and then sort it into the top 5 order. The main tables in question are T_Member, T_Fixture, T_Season and T_FixtureTeam:
T_Member:
MemberNo, FirstName, LastName, (etc)
T_Fixture:
FixtureNo, SeasonNo, MatchDate, (etc)
T_Season:
SeasoNo, SeasonDescription, SeasonStartDate, SeasonEndDate, CurrentSeason
T_FixtureTeam:
FixtureNo, MemberNo, 2Points, 3Points,Fouls,FreeShotAttempts,FreeShotsHit,Assists
Where you see the same field name, that field data is common for all tables that it appears in. In other words, for example, the T_FixtureTeam 'FixtureNo' and 'MemberNo' fields are made up of 'T_Fixture.FixtureNo' and 'T_Member.MemberNo' respectively.
Ready?
The user will select a season at the top of the page (although there's a default to the current season (CurrentSeason is a Boolean field). The query needs to:
- Find all fixtures in that season
- Find all members who played in those fixtures
- Find the top 5 3-point scorers (or whatever stat I'm gathering) for those matches
- Sort the resulting data
I figure I need a JOIN on MemberNo but, as I say, the rest of it has my head spinning.
TIA
View 1 Replies
View Related
Feb 8, 2006
I'm receiving "a generic error occurred in gdi+" processing error while
running a preview of my report inside visual studio. When this report
is opened by a client, their browser will just hang (Not Responding).
Other reports don't have error. The report will not render any chart although thousands of record will be need to output.
Hope anyone can help. Thanks!
View 5 Replies
View Related
Apr 9, 2008
I have to make a store procedure for every table like
create proc ins_all (@alltable varchar(100),@allfields nvarchar(1024),@allvalues nvarchar(1024))
as
insert into @alltable (@allfields) values (@allvalues)
this procedure generates error, Can anyone give a good idea for the required procedure
Thanks in advance
View 6 Replies
View Related
Oct 16, 2007
Hi,
We want our database to be unicode complaint and i need a generic collation that should do the work irrespective of the fact what data is entered in the database. Can someone suggest me any generic collation for that.
Thanks and regards
Salil
View 3 Replies
View Related
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
View Related
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
Jun 20, 2006
Hey I'm using this DAL to populate a generic list called assetDetailListHowever I'm adding a 2nd result set to my stored proc, protected void GenerateAssetDetails<T>(DbDataReader dataReader, ref List<AssetDetails> assetDetailList) { while (dataReader.Read()) { // Create a new culture AssetDetails assetDetail = new AssetDetails(); // Set the culture properties assetDetail.FileName = dataReader.GetString(dataReader.GetOrdinal("filename")); assetDetail.Title = dataReader.GetString(dataReader.GetOrdinal("title")); assetDetail.AltTag = dataReader.GetString(dataReader.GetOrdinal("alt_tag")); assetDetailList.Add(assetDetail); dataReader.NextResult();// this is the 2nd result set AssetContent assetContent = new AssetContent(); assetContent.content_id = dataReader.GetInt32(dataReader.GetOrdinal("content_id")); how do I creatre another list .Add(assetContent); } // Close and dispose of the reader dataReader.Close(); dataReader.Dispose(); } #endregion }
View 2 Replies
View Related
Jul 18, 2007
Hi,
This is really a Visual Studio 2005 Report Designer problem but I didn't see a good place to post this question in the Visual Studion forum.
On some reports when looking at the Data tab the Generic Query Designer is disabled. Anybody know why? The report runs. Other reports have it enabled.
I am using Visual Studio 2005 sp1.
Thanks,
Darren
View 1 Replies
View Related
Apr 25, 2007
Hi everyone,
I€™ve got a few Script Task in my dtsx. Up to the moment I don€™t need have generic functions but I was wondering if it is possible to use a such functions.
I attach you a sample of I say you:
Script Task1
General()
ScriptTask2
Stuff
WritingLog()
ScriptTask3
Stuff
Call General()
Thanks for your thoughts,
View 3 Replies
View Related
Jan 10, 2008
Here is my situation. I have a fairly large query that I am working on in visual studio (data tab). I am at the point where the program will NOT let me type (add SQL code) anymore. I found that clicking the Generic Query Designer (turning the query builder on) button switches the format then allows me to add more code. Well, this solves the running out of room issue, BUT now it does something to my code and it will not process it. Prior to clicking the Generic Query Designer button, everything worked w/ no errors. I did not change anything, but simply pressed the Generic Query Designer button (turning the query builder on) and now it dose not work now. Just for kicks, I have pasted my working origional code into SSMS, added code and it executed just fine in SSMS. But then I pasted back into visual studio and it gave me the same errors?
Has anyone experienced this? Is there a maximum for lines of code? Why does the Generic Query Designer (having query builder on) change my code? All comments are welcome!
-Smylie
View 6 Replies
View Related
Jun 28, 2007
Hello every body.
First of all sorry for my bad english but I'm french ;-)
My internship consits into making a generic table exporter (with a table list). Export into csv files.
I have tried 2 solutions :
1 - Create a DTS with a Dynamic Properties Task. The problem is I can easily change the destination file but when I change the table source I don't know how to remap the transformations between source and destination (do you see what I mean ?). Any idea ?
2 - Use the bcp command. Very simple but how to do when tables contains the separator caracter ? for example : If a table line is "toto" | "I am , very happy" --> the csv file will look like this : toto, I am , very happy --> problem to get back the data ( to much comma ).
Does someone has a solution ?
Last point is how to export the table structure ? For the moment, using the table structure, I generate an sql query which creates a table (I write this query in a file). Isn't there any "cleaner" solution ?
Thanks in advance and have a nice day all
View 3 Replies
View Related
Feb 26, 2008
Hi-
I have a number of tables that contain data that we want to concatenate as a part of our reporting processes. Instead of building a whole series of functions that each concatenate data from only one table, I want to create a function (or functions that work together) to concatenate data from ANY specified table, since the logic is identical.
The basic logic is:
Code Snippet
DECLARE @x varchar(max)
DECLARE @theReturn varchar(max)
DECLARE theValues CURSOR FOR
SELECT DISTINCT TOP 100 PERCENT <col name to concatenate>
FROM <the table in question>
WHERE <the identity column in the table> = <a specific value>
ORDER BY <col name to concatenate>
SET @x = ''
SET @theReturn = ''
OPEN theValues
FETCH NEXT FROM theValues INTO @x
WHILE @@FETCH_STATUS = 0
BEGIN
SET @theReturn = @theReturn + '; ' + @x
FETCH NEXT FROM theValues INTO @x
END
CLOSE theValues
DEALLOCATE theValues
SET @theReturn = LEFT(@theReturn, Len(@theReturn) - 2)
RETURN @theReturn
The problem I am running into is how to create the "generic" or "abstract" version of the SELECT statement in the cursor. I can certainly pass in the values surrounded by < and > as arguments, but I cannot figure out how to insert them into the SQL and make it work. I can't create a text string and then use the EXEC statement, because in a scalar function SQL Server won't allow it.
Any thoughts, work-arounds, or solutions?
Thanks-
View 5 Replies
View Related
Oct 20, 2006
I want to use ONE stored procedure to update/insert about 300 tables in a database. I hoped this code would work but it gives me an error:
ALTER PROCEDURE [dbo].[InsertRowsTick]
@tableName varchar,
@bid float,
@ask float,
@last float,
@volume int,
@dateTimed DateTime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO tableName (
bid,
ask,
last,
volume,
dateTimed)
Values (
@bid,
@ask,
@last,
@volume,
@dateTimed)
END
When I (1) commented out @tableName parameter and (2) instead of INSERT INTO tableName wrote: INSERT INTO <realtableName> the code (C#) executed and the table was filled.
How can I use a generic table name as a parameter?
Thanks.
View 9 Replies
View Related
Oct 7, 2015
I need the SQL to calculate the duration between a day and time, no specific date.Â
For example:
Sun 00:00 > Mon 08:00 = 5.67 days
So there is no date, it's just the general duration (in days), from a specific day and time to a specific day and time.
View 3 Replies
View Related
Apr 3, 2007
It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
Thanks,
John
View 3 Replies
View Related
