February 17, 2009 at 6:03 am
I have just designed a database that contains 9 tables. Six of the tables are filled with data that will not change. The three remaining tables are empty right now but related by keys. I am confused how to insert data into these tables and in what order they need to be populated. What additional information do I need to provide to enable someone to help me with my problem?
I have read books but they just give simple insert statements into one table and don't help me with more complex inserts.
February 17, 2009 at 6:25 am
To get someone to help you further, you have to post your database design. Also, you mentioned that the examples that you have read only insert data into one table at a time. That's because you can only insert data into one table at a time. You can do all of your inserts in a transaction so that it rolls back if there is an error. The order in which you insert data is going to be dictated by your design.
Greg
February 17, 2009 at 7:02 am
My database is designed to store information and filepaths for all the documents in our organization.
Here are my tables:
tblDocument
PK DocID int, not null
FK DocTypeID smallint, not null
FK DivisionID smallint, not null
DocDesc nvarchar(50), null
Filename nvarchar(25), null
Filepath nvarchar(100), null
InsertDate datetime, null
UpdateDate datetime, null
FK LangID smallint, null
tblPDF
PK PDFID int, not null, related to DocID 1:1
MNumber nvarchar(15), null
ModelID smallint, null
PDFTypeID smallint, not null
tblModel
PK ModelID smallint, not null
ModelDescription nvarchar(100), not null
FK BrandID smallint, null
FK ProductTypeID smallint, not null
FK SizeID smallint, null
These are the three tables that are now empty and need to be filled. The other six tables are filled with data that will not change. tblModel will contain a reference to a value in three of the filled tables (Brand, ProductType, Size) and tblDocument will reference the DocType, Division, and Language tables.
My problem is determining how to insert data in the three empty tables in the proper order so that I do not run into problems with foreign key constraints. Not every document has a Model associated with it (i.e. a general training manual) and right now the only documents I am concerned with are PDFs. Right now I have the primary key in the Document table related to the primiary key in the PDF table.
I have over 600 pdf files I have to open individually to get their MNumber if they have one and any model, brand, ProductType, and Size information they may contain. Then I have to place the appropriate id number in the tblDocument and tblModel tables.
February 17, 2009 at 7:36 am
First, the six lookup tables shouldn't be an issue with the other three tables as long as the data that you are inserting is in the lookup table.
It looks like tblDocument is your main table. tblPDF is a subset table with fields that only apply to PDFs. And tblModel is an optional table that only relates to tblPDF.
So, tblDocument should be the first table that you insert data into. It should contain one record for every document that you have. You will have to keep track on the DocumentID so that it can be used in tblPDF.
The next two tables could be handled two different ways. Because ModelID is nullable in tblPDF. You could insert into tblPDF first and leave ModelID null. Then, insert data into tblModel where applicable. Finally go back and update ModelID in tblPDF.
Probably a better way would be to conditionally insert records into tblModel. Then insert into tblPDF and insert the appropriate ModelID when it exists.
Hope that helps,
Greg
February 17, 2009 at 7:44 am
thank you, greg, that does help.
now i just need to figure out how to make this work with strongly typed datasets in visual studio 2005
February 17, 2009 at 7:47 am
it is a common design issue to just use nullable columns !
DocDesc nvarchar(50), null
Filename nvarchar(25), null
Filepath nvarchar(100), null
InsertDate datetime, null
UpdateDate datetime, null
FK LangID smallint, null
Avoid the use of NULL whenever you can !
If in this case a blanc value causes the same behavior as when that columns is NULL, make it NOT NULL !
for columns like insertdate en updatedate, assign a default value
InsertDate datetime not null default getdate()
Many of us tend to support a column "updatedate" with a trigger for update.
to be sure the columns value is updated every time and not only if someone provides a value for it.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 17, 2009 at 9:30 am
ALZDBA (2/17/2009)
it is a common design issue to just use nullable columns !DocDesc nvarchar(50), null
Filename nvarchar(25), null
Filepath nvarchar(100), null
InsertDate datetime, null
UpdateDate datetime, null
FK LangID smallint, null
Avoid the use of NULL whenever you can !
If in this case a blanc value causes the same behavior as when that columns is NULL, make it NOT NULL !
for columns like insertdate en updatedate, assign a default value
InsertDate datetime not null default getdate()Many of us tend to support a column "updatedate" with a trigger for update.
to be sure the columns value is updated every time and not only if someone provides a value for it.
Thank you, ALZDBA. I have made adjustments to my database as you suggested. The only thing I left null was the ModelID in the tblPDF because I was not sure what to put in as a default value. The ModelID is a primary key in the tblModel and a foreign key in the tblPDF. Of course, not all PDFs will have a model associated with them, so that is why I left ModelID null in the tblPDF. Is there a default value I could use for ModelID in tblPDF that will not get me into trouble when I start adding models?
Also, I am interested in the trigger idea you gave me for updating the UpdateTime. I'm new to that. I'm like a person who has been around water for some time but hasn't learned to swim yet, and now I find myself in the middle of the lake.
February 18, 2009 at 12:07 am
rhjohnson (2/17/2009)
Thank you, ALZDBA. I have made adjustments to my database as you suggested. The only thing I left null was the ModelID in the tblPDF because I was not sure what to put in as a default value. The ModelID is a primary key in the tblModel and a foreign key in the tblPDF. Of course, not all PDFs will have a model associated with them, so that is why I left ModelID null in the tblPDF. Is there a default value I could use for ModelID in tblPDF that will not get me into trouble when I start adding models?Also, I am interested in the trigger idea you gave me for updating the UpdateTime. I'm new to that. I'm like a person who has been around water for some time but hasn't learned to swim yet, and now I find myself in the middle of the lake.
If the ModelID is not mandatory, then you have a valid case to leave it null.
Just be sure to add a Foreign key constraint and to support it with an index that matches exact the FK-definition.
The trigger could be like this:
create trigger [dbo].[tru_yourtable] on [dbo].[yourtable]
for update
as
begin
update P
set TsUpdate = getdate()
, UserUpdate = suser_sname()
from dbo.yourtable P
inner join inserted I -- implicit object that contains the new data
on I.IdNr = P.IdNr -- IdNr = primary key
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply