Help - Best way to store related data (across or down)?

Good day.
I have a form with approx 90 related components. All 90 components are
associated with an App_ID. An App_ID can occur more than once but with
different data from the 90 fields.
I simply cannot decide whether to store the data from each component in
it's own column or as a separate row.
Any suggestions would be greatly appreciated.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***I think you hit on the key word here "related". To me that automatically
implies that you would set up a separate, but related, table to contain your
90 components. Without knowing more than you said about your app, I would
probably store each related component in its own row in the second table.
My assumption is that not all 90 components would necessarily be populated
for all App_ID's and that if, in the future, you add a 91st component you
don't necessarily want to change your whole schema.
Here's some example DDL. It contains 3 related tables, Apps which contains
your AppID and other App-specific info, AppComponents which contains an
App-specific component list, and LookupComponents which contains
Component-specific information (in this case just ComponentID and Name):
CREATE TABLE [dbo].[AppComponents] (
[AppID] [int] NOT NULL ,
[ComponentID] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Apps] (
[AppID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
CREATE TABLE [dbo].[LookupComponents] (
[ComponentID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
ALTER TABLE [dbo].[AppComponents] WITH NOCHECK ADD
CONSTRAINT [PK_AppComponents] PRIMARY KEY CLUSTERED
(
[AppID],
[ComponentID]
)
GO
ALTER TABLE [dbo].[Apps] WITH NOCHECK ADD
CONSTRAINT [PK_Apps] PRIMARY KEY CLUSTERED
(
[AppID]
)
GO
ALTER TABLE [dbo].[LookupComponents] WITH NOCHECK ADD
CONSTRAINT [PK_LookupComponents] PRIMARY KEY CLUSTERED
(
[ComponentID]
)
GO
ALTER TABLE [dbo].[AppComponents] ADD
CONSTRAINT [FK_AppComponents_Apps] FOREIGN KEY
(
[AppID]
) REFERENCES [dbo].[Apps] (
[AppID]
),
CONSTRAINT [FK_AppComponents_LookupComponents] FOREIGN KEY
(
[ComponentID]
) REFERENCES [dbo].[LookupComponents] (
[ComponentID]
)
GO
"bajan_rick" <bajanrick@.devdex.com> wrote in message
news:eaMBbw1QFHA.4028@.tk2msftngp13.phx.gbl...
> Good day.
> I have a form with approx 90 related components. All 90 components are
> associated with an App_ID. An App_ID can occur more than once but with
> different data from the 90 fields.
>
> I simply cannot decide whether to store the data from each component in
> it's own column or as a separate row.
>
> Any suggestions would be greatly appreciated.
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> I have a form with approx 90 related components. All 90 components
are associated with an App_ID. An App_ID can occur more than once but
with different data from the 90 fields. <<
Do not confuse the forms that collect data with the data model and the
tables in the schema. If they are all attributes of the same entity,
then they belong in one row in the table. If a subset of columns is
another entity, then it needs to be in its own table.
Then we have to ask if "app_id" is really a key or not. It sounds like
a sequential form number (remember the "Bates Numbering Stamp" in the
old days?) that has nothing to do with the data model, but is used for
data collection control.
Without any real information, it is imposible to give specific advice.
But a while a 90 column table is possible, it is rare. I would guess
-- I mean guess -- that this is really a 5 to 6 column schema.
The worst thing you can do is build an EAV design or some other variant
that mixes data and metadata in the name of a "flexible" design. They
fall aprt in about a year of production work after running insanely
slow.|||Thanks guys.
I'm not if I can explain the app anymore. I guess it's more like a
spreadsheet where there is the possiblit of multiple rows being
represented by 1 App_ID (PK).
My table is currently:
CREATE TABLE [dbo].[App_Sheet] (
[SheetID] [int] NOT NULL ,
[AppID] [int] NOT NULL ,
[Component_ID] [int],
[Value] [varchar] (50) NULL
)
GO
CELKO:
What do you mean by
"..this is really a 5 to 6 column schema"
*** Sent via Developersdex http://www.examnotes.net ***