Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135

    Unanswered: Adding a new table to an existing database structure?

    Hi all, How are you?

    I Have a question regarding the above heading. I'm certain you can add tables to an existing database, but what are the complications, concerns you should bare in mind? Can I add it anywhere in the database structure or should it be added to the nearest table in the structure?

    Essentially, I was hoping to get some pointers before I add a new table in my database.

    Kind regards
    MG

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    From what you state in your question, you clearly need to read a good database book or take a course in database design/development. Creating tables, querying the database, adding and altering data in the tables, ... none of it is difficult, but you should really get an understanding of what it is before you start "adding a new table near the nearest table in the structure".

    The best way is to start with your own personal SQL Server database, perhaps a restore of a backup of your production database. Make sure you can not accidentally log in into the production database.
    From your recent postings I deduct you have SQL Server up and running. Just make sure you stay away from your production database. That way you are unconstrained to experiment.

    This is most certainly not the answer you were looking for, but IMHO it's the best answer for you right now.

    That all said, welcome to the database world!
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi thanks for the reply,

    I'm such a fan of honesty - so welcomed that answer, Lol.

    Im working on my localhost, and not touching and live company databases (that much i do know). I think my wording was probably not the best either.

    But you're right, i do need to read more and take some lessons on the ins and outs of databases/SQL. However that said, I have to state that im fresh in a job (about 8 months here), learning ASP, vbscript, Databases, T-SQL/SP for deadlines "managment" want yesterday, so learning as i go, hence the Forums friend.

    Keep my in mind will need all the help i can get :-)

    The question really is this, and hope you can give guidance.

    I need to create a table that will be used to hold names of .pdf files, and so with ASP user can select the file they want in a drop down menu and load it. Like a recordset of images, but applied for pdfs?

    Essentially, if i create two tables tbl1 and tbl2, and cross ref the primary key / foreign key, is the relationship built, or does some thing need to be done in the database deign/diagram view to make the one-many rel'ship etc?

    Any info would be great

    Regards
    MG
    Last edited by mind_grapes; 08-12-09 at 12:05.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What information do you want to store in that table about those PDFs? I guess file name for starters, but probably also the URL, ...

    How will your users find the PDF they want? Select the one they need in an ever growing alphabetically ordered list or by first preselecting the category(ies)? Probably you want to store multiple tags (categories) for one PDF file, like "Computer", "Software", "Hardware", "Network", "Wiki", "database", ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi thanks for reply.

    Im not entirely sure to be honest, its the first time i'll be creating one, but id guess that Pdf name, pdf detail, perhaps pdf number? cant image I'd need any more then that?

    The user will be presented with the list of the pdfs in a drop down menu, something i can pull from the database with the use of a stored procedure i think?

    In total there will probably be about 20-30 pdfs, all of which will be displayed alphabetically in on one drop down menu.

    Regards
    MG

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    create table dbo.files(
    	fileId	bigint	IDENTITY(1, 1)	not null,
    	Name	VARCHAR(256)	not null,
    	Detail	VARCHAR(256)	not null,
    	path	VARCHAR(256)	not null,
    	fileType	CHAR(4)	NOT NULL
    		CONSTRAINT CC_Type CHECK(fileType IN ('PDF', 'DOC', 'DOCX', 'XLS'))
    	constraint PK_files primary key  (fileId)
    )
    
    SELECT fileId, Name 
    FROM dbo.files
    By providing an extra column fileType, you make the table more general, making it suitable to store other documents than only PDF files. This is also reflected in the table name.

    A better approach than the extra CHAR column fileType, is to create an extra lookup table CoFileType , and make a reference to it from Files, by adding a column CoFileTypeId (instead of fileType).
    Code:
    create table dbo.CoFileType (
       CoFileTypeId		bigint		not null,
       Code			char(5)		not null,
       ShortDescription	varchar(100)	not null,
       LongDescription	varchar(250)	not null,
       constraint PK_CoFileType primary key  (CoFileTypeId)
    )
    
    create table dbo.files(
    	fileId		bigint	IDENTITY(1, 1)	not null,
    	Name		VARCHAR(256)	not null,
    	Detail		VARCHAR(256)	not null,
    	CoFileTypeId	BIGINT			not null,
    	URL		VARCHAR(256)	not null,
    	constraint PK_files primary key  (fileId)
    )
    
    alter table dbo.files
    add constraint FK_files_CoFileType foreign key (CoFileTypeId)
    	references dbo.CoFileType (CoFileTypeId)
    edit: corrected typo : "CoFileTipeId" should have been "CoFileTypeId"
    Last edited by Wim; 08-14-09 at 05:33.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi, thanks for the post.

    So you'd suggest the second way, when creating the tables?
    I shall try this and check back with you.

    I do have a question regarding database integrity.

    If I create the new tables and join them to the table i think they should be joined to, but realise after that they're on the wrong table, will this mess the integrity of the entire database? What should i bare in mind?

    Thanks once again

    Regards
    MG

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    If I create the new tables and join them to the table i think they should be joined to, but realise after that they're on the wrong table, will this mess the integrity of the entire database? What should i bare in mind?
    What do you mean?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  9. #9
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hello Wim,

    What i mean, is, will the constant adding / deleting of tables have an adverse affect on the database in any way?

    If i create a new table, as you have suggested above, and then join this table to an old one (by foreign key relationship), but then realise that its inccorect in some way, and then delete it, and start again, will this impact the structure / processing of the database in any way?

    Hope this makes sense? if not please say

    Regards
    MG

  10. #10
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by mind_grapes
    If i create a new table, as you have suggested above, and then join this table to an old one (by foreign key relationship), but then realise that its inccorect in some way, and then delete it, and start again, will this impact the structure / processing of the database in any way?
    No, not if you do it properly. In DB2, if you DROP a table, all related objects are dropped with it: FK's to and from that table, indexes, views that use that table, triggers, ... In SQL Server you have to DROP the FK's and triggers yourself. If you do that properly, your database will stay healthy.

    But to help you, God created Development, Test and Production databases on the eighth day.

    Often the Development database is the restore of a recent backup of the Production databases. Consider the development database as your sandbox where you can explore, test, restart, ... and do other fun things. And if you FUBAR, just do a restore of the latest production backup.

    Once you're pleased with the result, you execute the DDL-scripts you have used to alter your development database, on the Test database (a restore from the most recent production database backup) and let your users play with your application. The Test system is your user's sandbox.

    Only when your users are happy with the result you apply the DDL-scripts to the production system.

    So there goes some time and tinkering between the first concept in the development database and the final code that gets executed on the production system. It wouldn't hurt the production system to do all that tinkering on it (if done properly) instead of on the dev database, but it's just far too risky to do. On the other hand, developing on production would be harder. Consider testing the output of a query while users are adding, altering and deleting data at random places.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  11. #11
    Join Date
    Jun 2009
    Location
    Midlands
    Posts
    135
    Hi Wim, thank you for the reply,

    Thats a lot of information to take in, but I shall try and learn and take this knowledge on board.

    I think I'm just weary of breaking something or doing some damage as I am still learning, but this will help in the future.

    I'm toying with the idea of re-working some tables and having a play around with those, so that'll probably be the route I take.

    I also have another question I hope you can clarify. I should probably start a new thread, and, if you think its best to do that i will, but hope you can help?

    With Store Procedures I know you have a great deal of control, but can you do the following?

    If, for example, I've created a table (table X) within a database, can I then write a SP that allows users to add a new column to table X when they want? (Incase something changes in future).

    Not sure if its the best idea, as they may just add columns randomly, but was wondering if its possible.

    Any info would be great.

    Kind regards
    MG

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •