I have this scenario:
One big table which will be populated with playlists; each playlist is
defined by:
1) tv channel
2) playlist date
Each playlist has many rows; each rows is defined by:
1) tv channel (as said before)
2) playlist date (as said before)
3) playlist onair hour
I must keep in this table these data for example for 2 months with 10
channels and 1000 rows for each playlist.
In my import procedure I have to do some calculations, so I prefer to load
data first in a temporary table, do my calculations and then make an insert
block of all 1000 rows in 1 step with a stored procedure from the temporary
table to the target table when all calculations and editing is ended: insert
into maintable execute('select * from temporarytable'). Then delete the
content of temporary table and import another playlist.
What kind of table structure would you use?
I think the best choice would be a multiple primary key for each row with
the fields 1,2 and 3.
Do you see some better solution? Is a multiple primary key very expensive in
terms of database performance?
I think using an autoincrement primary ID would not help because the insert
block would not work with it.
Any ideas?
Thanks!
Jem777> I think using an autoincrement primary ID would not help because the
> insert
> block would not work with it.
Not sure what you mean, because if you cannot insert data into the table, it
would be valueless for everyone. Either way, you need to have a valid
natural key of some sort defined or you will get logically duplicated data
(the rows may be different, other than the meaningless key)
> Do you see some better solution? Is a multiple primary key very expensive
> in
> terms of database performance?
Your first concern should be the validity of the data. If you only have one
table, and it is not related to any other tables, then the size of the key
will make very little (if any) difference
> I think the best choice would be a multiple primary key for each row with
> the fields 1,2 and 3.
> 1) tv channel (as said before)
> 2) playlist date (as said before)
> 3) playlist onair hour
This is probably a good key, because there is no way you will have two
programs on the same TV channel at the same time. I will go out on a limb
and say that this is not a great table structure, and is going to be wildly
denormalized, but I don't know what you are doing with it, so who knows if
it matters :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"jem777" <camillo@.rockit.it> wrote in message
news:eVBKSj5OFHA.1440@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I have this scenario:
> One big table which will be populated with playlists; each playlist is
> defined by:
> 1) tv channel
> 2) playlist date
> Each playlist has many rows; each rows is defined by:
> 1) tv channel (as said before)
> 2) playlist date (as said before)
> 3) playlist onair hour
> I must keep in this table these data for example for 2 months with 10
> channels and 1000 rows for each playlist.
> In my import procedure I have to do some calculations, so I prefer to load
> data first in a temporary table, do my calculations and then make an
> insert
> block of all 1000 rows in 1 step with a stored procedure from the
> temporary
> table to the target table when all calculations and editing is ended:
> insert
> into maintable execute('select * from temporarytable'). Then delete the
> content of temporary table and import another playlist.
> What kind of table structure would you use?
> I think the best choice would be a multiple primary key for each row with
> the fields 1,2 and 3.
> Do you see some better solution? Is a multiple primary key very expensive
> in
> terms of database performance?
> I think using an autoincrement primary ID would not help because the
> insert
> block would not work with it.
> Any ideas?
> Thanks!
> Jem777
>|||You made me think about it and maybe I found a solution!
MAINTABLE:
3 id_palinsesto int 4 0 (primary key)
0 id_tmp int 4 0
0 data datetime 8 0
0 rete int 4 0
0 ora int 4 -1
TMPTABLE:
3 id_tmp int 4 0 (primary key)
0 data_palinsesto datetime 8 1
0 rete int 4 1
0 ora int 4 1
The copy query:
insert into MAINTABLE(id_tmp,data,rete,ora) execute('select
id_tmp,data,rete,ora from TMPTABLE)
The 2 primary keys are not linked each other!!!!
I did not know it was possible!!!
I dont have to use multiple primary keys!!!
COOL!
Thanks,
jem777
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uQY20s5OFHA.1396@.TK2MSFTNGP10.phx.gbl...
> Not sure what you mean, because if you cannot insert data into the table,
it
> would be valueless for everyone. Either way, you need to have a valid
> natural key of some sort defined or you will get logically duplicated data
> (the rows may be different, other than the meaningless key)
>
expensive
> Your first concern should be the validity of the data. If you only have
one
> table, and it is not related to any other tables, then the size of the key
> will make very little (if any) difference
>
with
>
> This is probably a good key, because there is no way you will have two
> programs on the same TV channel at the same time. I will go out on a limb
> and say that this is not a great table structure, and is going to be
wildly
> denormalized, but I don't know what you are doing with it, so who knows if
> it matters :)
>
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Blog - http://spaces.msn.com/members/drsql/
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "jem777" <camillo@.rockit.it> wrote in message
> news:eVBKSj5OFHA.1440@.TK2MSFTNGP10.phx.gbl...
load
with
expensive
>|||>> I think the best choice would be a multiple primary key for each row
with
the fields [sic] 1,2 and 3. <<
In SQL Server, the temporal data type has both the date and time.
Generally speaking, you want to show durations of time, not points. You
also never told us what is playing -- TV shows? Here is my guess.
CREATE TABLE PlayLists
(tv_channel CHAR (5) NOT NULL,
time_slot_start DATETIME NOT NULL,
time_slot_end DATETIME NOT NULL,
CHECK (time_slot_start < time_slot_end),
show_name CHAR(15) NOT NULL,
PRIMARY KEY (tv_channel, time_slot_start ));
You can now use BETWEEN predicates with the time slot durations for
your queries.
performance? <<
No. Size, grandularity and the order of the columns in the declaration
are more important.
insert block would not work with it. <<
You do know that an autoincrement extension is not relational,
proprietary and dangerous to data integrity, don't you? By definition
it can never be a key. You already have a natural thanks to the
physics of the situation, so quit trying to mimic a magnetic tape file.|||>>> I think using an autoincrement primary ID would not help
> insert block would not work with it. <<
> You do know that an autoincrement extension is not
> relational,
> proprietary and dangerous to data integrity, don't you?
> By definition
> it can never be a key. You already have a natural thanks
> to the
> physics of the situation, so quit trying to mimic a
> magnetic tape file.
Run for your lives! Data integrity will be lost using
identity columns! Check.
Hate to tell you Joe, but most younger people don't even
know anyone that has even heard of someone using mag tapes.
;->
Suppose, the tv_channel description for a given entity
changes or is entered wrong? Same entity, different
description. That means that all tables that join to
Playlists will also have to be changed along with all
records in the Playlists table. Suppose on entry, you don't
know the tv_channel and do know the timeslot or the other
way around or don't know either for sure? That means that
entity could not be entered into the system until that is
known or a bogus value must be entered. (Other logic can
determine whether a given Playlist item was valid (meaning
it needed a channel and timeslot)).
Using TV_Channel/Timeslot only works if both values are
absolutely know at time of entry and have an extraordinarily
high probability that they will not change over the lifetime
use of the application.
Thomas|||> No. Size, grandularity and the order of the columns in the declaration
> are more important.
Just to clarify, because when I first read this statement it seemed a bit
ambiguous. You are speaking of the declaration of the primary key, not of
the table/columns.|||On Thu, 7 Apr 2005 20:34:40 +0200, jem777 wrote:
(snip)
>The copy query:
>insert into MAINTABLE(id_tmp,data,rete,ora) execute('select
>id_tmp,data,rete,ora from TMPTABLE)
Hi jem777,
Are you aware that you don't need to use dynamic SQL for this?
INSERT INTO MainTable (id_tmp,data,rete,ora)
SELECT id_tmp,data,rete,ora
FROM TmpTable
will work just as well!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Suppose, the tv_channel description for a given entity changes or is
entered wrong? Same entity, different
description. <<
We could use a reference to another table of televison stations which
is keyed on call letters, and let DRI actions handle this.
know at time of entry and have an extraordinarily
high probability that they will not change over the lifetime use of the
application. <<
I am pretty certain that time will keep going on forever and that I can
break it into timeslots. I can then handle changing the call letters
with DRI actions the few rare times that happens. My one problem is
not knowing the show that is going to run. Having consulted at BRTN in
Belgium, I know that there is no industry standard for identifying
television shows.
I might want to allow time_slot_end to be NULL, so i can have
open-ended time slots.|||
> I am pretty certain that time will keep going on forever and that I can
> break it into timeslots.
Is this really something you want to risk? Stranger things have happened,
you know. Heck, just a few centuries ago they changed our caledar around. An
d
then there's crazy always changings things like daylight savings time.
Besides, what happens if they'll want to run shows in such a small timeslot
that SQL Server can't even differentiate between STart and End?
No, I say make a [DateTime] table: ID IDENTITY INT, DATE DATETIME. That way,
if the definition of "April 07, 2005 09:45PM" ever changes, you'll be set.
-- Alex Papadimoulis
As if it wasn't obvious, it's MonthID 6 (I misspelled February wrong twice
in my calendar and had to delete those entries). I have three more w
s ofthis!|||<snip>I am pretty certain that time will keep going on forever ...</snip>
I don't know about that, Joe! I just got done with a book on G?del and
Enstein that says There's no such thing as Time...
"--CELKO--" wrote:
> entered wrong? Same entity, different
> description. <<
> We could use a reference to another table of televison stations which
> is keyed on call letters, and let DRI actions handle this.
>
> know at time of entry and have an extraordinarily
> high probability that they will not change over the lifetime use of the
> application. <<
> I am pretty certain that time will keep going on forever and that I can
> break it into timeslots. I can then handle changing the call letters
> with DRI actions the few rare times that happens. My one problem is
> not knowing the show that is going to run. Having consulted at BRTN in
> Belgium, I know that there is no industry standard for identifying
> television shows.
> I might want to allow time_slot_end to be NULL, so i can have
> open-ended time slots.
>
No comments:
Post a Comment