designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like University offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like University one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
.........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincer
Emre DNER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like University offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like University one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
> of the customer..
> the listing of the centers and their info is straightforward however when it
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
David Portas

designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like university offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like university one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincerEmre D=DDN=C7ER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like university offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like university one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCente=[/vbcol
]
rs[vbcol=seagreen]
> of the customer..
> the listing of the centers and their info is straightforward however when =[/vbcol
]
it[vbcol=seagreen]
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
David Portas

designing inheriting entities

Dear All,
our customer has 3 kinds of entites
the first one is the ApplicationCenter where students can register to
exams...
and there are types of app centers like University offices, high schools
etc.. these types goes to another table..
and finally each type has its own instances like University one ,
university2 , univ3 etc. which means there are several more tables Like
universities , highschools , examCenters etc...
the requirement is to have ADD/Edit/Delete screens of all ApplicationCenters
of the customer..
the listing of the centers and their info is straightforward however when it
is time to edit many entities needs to be updated at the same time
........
i am trying to figure out a table design where i can build sort of
inheriting entities..
any clue?
best regards..
emre dincerEmre D=DDN=C7ER wrote:
> Dear All,
> our customer has 3 kinds of entites
> the first one is the ApplicationCenter where students can register to
> exams...
> and there are types of app centers like University offices, high schools
> etc.. these types goes to another table..
> and finally each type has its own instances like University one ,
> university2 , univ3 etc. which means there are several more tables Like
> universities , highschools , examCenters etc...
> the requirement is to have ADD/Edit/Delete screens of all ApplicationCente=rs
> of the customer..
> the listing of the centers and their info is straightforward however when =it
> is time to edit many entities needs to be updated at the same time
> ........
> i am trying to figure out a table design where i can build sort of
> inheriting entities..
> any clue?
> best regards..
> emre dincer
It is possible to ensure that each common attribute appears only in
one place in the hierarchy for each type of entity. Therefore the
problem of updating the same attribute in multiple places won't arise.
Fifth Normal Form and the Principle of Orthogonal Design are two
principles that will help you achieve a good model. Google for them if
you aren't already familiar with them.
--
David Portas

Design Question

Here is my scenario:
I'm creating a thin client viewer/editor for a client that has a rebate
program.
There are several types of rebate programs, and incentives are different for
each. There are approximately 160 predicating companies and each "reports"
it's sales amount or other qualifying criteria each quarter.
Now, all this is pretty vanilla until, except that the rebate percentage for
each company can change in midstream of a fiscal year.
The old records (pre percentage) change must be kept for viewing only, but
post change must be editable and updateable.
What is the best way to accomplish this without having to nest a lot of
tables?
Thanks in advance for you input..
~ChrisI'd track the old and new percentage rates in a table and based on your
query date (Run from date) you select the proper percentage rate from
that table to use for the calculation.
Ex.
PercentageValidDate Percentage
1/1/2000 6.5
1/1/2003 6.85
6/1/2003 6.93
SELECT TOP 11 Percentage where PercentageValidDATE < [paramater run date]
order by PercentageValidDATE DESC
Smitty wrote:
> Here is my scenario:
> I'm creating a thin client viewer/editor for a client that has a rebate
> program.
> There are several types of rebate programs, and incentives are different for
> each. There are approximately 160 predicating companies and each "reports"
> it's sales amount or other qualifying criteria each quarter.
> Now, all this is pretty vanilla until, except that the rebate percentage for
> each company can change in midstream of a fiscal year.
> The old records (pre percentage) change must be kept for viewing only, but
> post change must be editable and updateable.
> What is the best way to accomplish this without having to nest a lot of
> tables?
> Thanks in advance for you input..
> ~Chris
>

Design Idea

Need some design ideas.
I have to create a table which will have 2 types of IDs
MainID SubID
1 1
1 2
2 1
2 2
3 100
3 101
Basically I want to auto generate SubID for different MainIDs. For 2
different MainIDs, SubIDs can be same. Once the starting number is
determined the increment is always by 1.
Any ideas how this can be achived?"XXX" <sa@.nomail.com> wrote in message
news:#BH3Wz5ZFHA.3120@.TK2MSFTNGP12.phx.gbl...
> Need some design ideas.
> I have to create a table which will have 2 types of IDs
>
> MainID SubID
> 1 1
> 1 2
> 2 1
> 2 2
> 3 100
> 3 101
>
> Basically I want to auto generate SubID for different MainIDs. For 2
> different MainIDs, SubIDs can be same. Once the starting number is
> determined the increment is always by 1.
> Any ideas how this can be achived?
>
A trigger would probable work in this situation.
Rick Sawtell
MCT, MCSD, MCDBA|||On Thu, 2 Jun 2005 13:59:32 -0400, XXX wrote:

>Need some design ideas.
>I have to create a table which will have 2 types of IDs
>
>MainID SubID
>1 1
>1 2
>2 1
>2 2
>3 100
> 3 101
>
>Basically I want to auto generate SubID for different MainIDs. For 2
>different MainIDs, SubIDs can be same. Once the starting number is
>determined the increment is always by 1.
>Any ideas how this can be achived?
>
INSERT INTO MyTable (MainID, SubID, OtherColumns)
SELECT @.MainID, -- Passed in as parameter
COALESCE((SELECT MAX(SubID)
FROM MyTable
WHERE MainID = @.MainID),
@.StartofSubID), -- Passed in as parameter
@.OtherColumns -- Passed in as parameters
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks Hugo.
It works. I think you missed the '+1' in your query.
INSERT INTO MyTable (MainID, SubID, OtherColumns)
SELECT @.MainID, -- Passed in as parameter
COALESCE((SELECT MAX(SubID)
FROM MyTable
WHERE MainID = @.MainID) +1 ,
@.StartofSubID), -- Passed in as parameter
@.OtherColumns -- Passed in as parameters
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:1suu91djb3l14fu50enarjf5u5dliagpan@.
4ax.com...
> On Thu, 2 Jun 2005 13:59:32 -0400, XXX wrote:
>
> INSERT INTO MyTable (MainID, SubID, OtherColumns)
> SELECT @.MainID, -- Passed in as parameter
> COALESCE((SELECT MAX(SubID)
> FROM MyTable
> WHERE MainID = @.MainID),
> @.StartofSubID), -- Passed in as parameter
> @.OtherColumns -- Passed in as parameters
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.