Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Table Design question



Permlink Replies: 2 - Last Post: Dec 2, 2014 2:05 PM Last Post By: Mats karlsson
Mats karlsson

Posts: 64
Registered: 11/8/99
Table Design question
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2014 10:37 AM
Hello,
I'm designing a database handling the elements in the periodic table
(the ones known).

I have designed a 'elements' table, containing the basic information on
each element, e.g. atom number and name.

As the data gets populated, I can see how I will want to add on more
fields later on. These 'extra' fields are extra information specific to
each element, e.g. density, boiling point etc, but may also be trivial
tidbits like history, discovery date for example.

Question is, as I can see I may want to add on literary hundreds of
added fields for each element, is it ok to just add the fields to the
'main' elements table? Something says not, but not sure.

I can also see how one could add new tables, say element_properties,
containing fields holding common properties and use a foreign key to the
elements table.

Whats the 'prefered' way, if any?
tk
Jeff Overcash (...

Posts: 1,529
Registered: 9/23/99
Re: Table Design question
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2014 11:35 AM   in response to: Mats karlsson in response to: Mats karlsson
totte karlsson wrote:
Hello,
I'm designing a database handling the elements in the periodic table
(the ones known).

I have designed a 'elements' table, containing the basic information on
each element, e.g. atom number and name.

As the data gets populated, I can see how I will want to add on more
fields later on. These 'extra' fields are extra information specific to
each element, e.g. density, boiling point etc, but may also be trivial
tidbits like history, discovery date for example.

Question is, as I can see I may want to add on literary hundreds of
added fields for each element, is it ok to just add the fields to the
'main' elements table? Something says not, but not sure.

I can also see how one could add new tables, say element_properties,
containing fields holding common properties and use a foreign key to the
elements table.

Whats the 'prefered' way, if any?
tk

What you are describing is a segregated table, 2 tables that are a 1:1 or 1:0
ratio. The main purpose for these tables usually are that as a whole the
records are very large (so takes time to bring across the wire to the client),
but you tend to work with them in very distinct subsets and rarely need all the
data. This of course can also be accomplished by not doing select * type
queries and only the columns you need.

The is nothing wrong with 100's of columns in a table as long as your DB can
have that. But if you have clear semantic splits in the data it is often
clearer in the schema the type of data included in the segregated tables (like
your element_properties).

If you go that route though I would suggest for clarity sake that you do not
wily nilly add to your secondary tables unless the data type really fits there
so it is easier to you without going back all the time and looking at the column
names to figure out what table you put that data in. That is probably one of
the bigger drawbacks to segregated tables ... what table did I put that data?

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Mats karlsson

Posts: 64
Registered: 11/8/99
Re: Table Design question
Click to report abuse...   Click to reply to this thread Reply
  Posted: Dec 2, 2014 2:05 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
On 12/2/2014 11:35 AM, Jeff Overcash (TeamB) wrote:


Whats the 'prefered' way, if any?
tk

What you are describing is a segregated table, 2 tables that are a 1:1 or 1:0
ratio. The main purpose for these tables usually are that as a whole the
records are very large (so takes time to bring across the wire to the client),
but you tend to work with them in very distinct subsets and rarely need all the
data. This of course can also be accomplished by not doing select * type
queries and only the columns you need.

The is nothing wrong with 100's of columns in a table as long as your DB can
have that. But if you have clear semantic splits in the data it is often
clearer in the schema the type of data included in the segregated tables (like
your element_properties).

If you go that route though I would suggest for clarity sake that you do not
wily nilly add to your secondary tables unless the data type really fits there
so it is easier to you without going back all the time and looking at the column
names to figure out what table you put that data in. That is probably one of
the bigger drawbacks to segregated tables ... what table did I put that data?

Thanks very much for that feedback. I can see the common sense of your
points. Saved me much time thinking over this design 'issue' :)

New to DB tables and so thought 20 columns is ginormous..
Thanks!
tk
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02