Skip to main content
Database Administrators

Return to Question

added 140 characters in body
Source Link
Tyler N
Tyler N
  • 113
  • 5

This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether'swhether animalsequipment's column to reference breedsmake-model.ID should be one field or two.

Say, there exists a table named breedsmake-model that stores dependent information.

ID DescriptionName Parent
1 DogAcme NULL
2 Lab1000 1
3 TerrierAnvil 1
4 CatOmni NULL
5 Siamese1000 4
6 SphynxPhotocell 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dogAcme
- lab1000
- terrierAnvil
catOmni
- siamese1000
- sphynxPhotocell

Notice that there are two 1000 models, but they belong to different makes, this is why the ID column is the key.

There is also a table named animalsequipment that stores information about the animalequipment, including the breedmake/model.

Based on user input, the breed is set and could be specific to labAnvil or stop at dogAcme. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dogAcme) and one that would show the more specific level (labAnvil).

As far as actual columns on the animalsEquipment table, what is the benefit of one column storing the most specific id given (23 if labAnvil, 1 if dogAcme) or two columns, one for each level?

So, for example Table A:

ID Name BreedMake-Model
1 Max 2
2 Spot 3
3 Fluffy 4

Vs. Table B:

ID Name Breed-Lvl1Make Breed-Lvl2Model
1 Max 1 2
2 Spot 1 3
3 Fluffy 4 NULL

My question is about the benefit of Table A's design vs. Table B's design

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether's animals column to reference breeds.ID should be one field or two.

Say, there exists a table named breeds that stores dependent information.

ID Description Parent
1 Dog NULL
2 Lab 1
3 Terrier 1
4 Cat NULL
5 Siamese 4
6 Sphynx 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dog
- lab
- terrier
cat
- siamese
- sphynx

There is also a table named animals that stores information about the animal, including the breed.

Based on user input, the breed is set and could be specific to lab or stop at dog. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dog) and one that would show the more specific level (lab).

As far as actual columns on the animals table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columns, one for each level?

So, for example Table A:

ID Name Breed
1 Max 2
2 Spot 3
3 Fluffy 4

Vs. Table B:

ID Name Breed-Lvl1 Breed-Lvl2
1 Max 1 2
2 Spot 1 3
3 Fluffy 4 NULL

My question is about the benefit of Table A's design vs. Table B's design

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether equipment's column to reference make-model.ID should be one field or two.

Say, there exists a table named make-model that stores dependent information.

ID Name Parent
1 Acme NULL
2 1000 1
3 Anvil 1
4 Omni NULL
5 1000 4
6 Photocell 4

This table is meant to represent a category/subcategory relationship that can be represented as:

Acme
- 1000
- Anvil
Omni
- 1000
- Photocell

Notice that there are two 1000 models, but they belong to different makes, this is why the ID column is the key.

There is also a table named equipment that stores information about the equipment, including the make/model.

Based on user input, the breed is set and could be specific to Anvil or stop at Acme. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (Acme) and one that would show the more specific level (Anvil).

As far as actual columns on the Equipment table, what is the benefit of one column storing the most specific id given (3 if Anvil, 1 if Acme) or two columns, one for each level?

So, for example Table A:

ID Name Make-Model
1 Max 2
2 Spot 3
3 Fluffy 4

Vs. Table B:

ID Name Make Model
1 Max 1 2
2 Spot 1 3
3 Fluffy 4 NULL

My question is about the benefit of Table A's design vs. Table B's design

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

added 408 characters in body
Source Link
Tyler N
Tyler N
  • 113
  • 5

This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether's animals column to reference breeds.ID should be one field or two.

Say, there exists a table named breeds that stores dependent information.

ID BreedDescription Parent
1 Dog NULL
2 Lab 1
3 Terrier 1
4 Cat NULL
5 Siamese 4
6 Sphynx 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dog
- lab
- terrier
cat
- siamese
- sphynx

There is also a table named animals that stores information about the animal, including the breed.

Based on user input, the breed is set and could be specific to lab or stop at dog. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dog) and one that would show the more specific level (lab).

As far as actual columns on the table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columnsAs far as actual columns on the animals table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columns, one for each level?

So, one for each level?example Table A:

IDNameBreed
1Max2
2Spot3
3Fluffy4

Vs. Table B:

IDNameBreed-Lvl1Breed-Lvl2
1Max12
2Spot13
3Fluffy4NULL

My question is about the benefit of Table A's design vs. Table B's design

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

This question is in the context of adding a new field or fields to a table.

Say, there exists a table named breeds that stores dependent information.

ID Breed Parent
1 Dog NULL
2 Lab 1
3 Terrier 1
4 Cat NULL
5 Siamese 4
6 Sphynx 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dog
- lab
- terrier
cat
- siamese
- sphynx

There is also a table named animals that stores information about the animal, including the breed.

Based on user input, the breed is set and could be specific to lab or stop at dog. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dog) and one that would show the more specific level (lab).

As far as actual columns on the table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columns, one for each level?

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

This question is in the context of adding a new field or fields to a table. - Specifically in my following example, whether's animals column to reference breeds.ID should be one field or two.

Say, there exists a table named breeds that stores dependent information.

ID Description Parent
1 Dog NULL
2 Lab 1
3 Terrier 1
4 Cat NULL
5 Siamese 4
6 Sphynx 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dog
- lab
- terrier
cat
- siamese
- sphynx

There is also a table named animals that stores information about the animal, including the breed.

Based on user input, the breed is set and could be specific to lab or stop at dog. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dog) and one that would show the more specific level (lab).

As far as actual columns on the animals table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columns, one for each level?

So, for example Table A:

IDNameBreed
1Max2
2Spot3
3Fluffy4

Vs. Table B:

IDNameBreed-Lvl1Breed-Lvl2
1Max12
2Spot13
3Fluffy4NULL

My question is about the benefit of Table A's design vs. Table B's design

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.

Source Link
Tyler N
Tyler N
  • 113
  • 5

One field vs multiple fields to reference ID values of dependent information

This question is in the context of adding a new field or fields to a table.

Say, there exists a table named breeds that stores dependent information.

ID Breed Parent
1 Dog NULL
2 Lab 1
3 Terrier 1
4 Cat NULL
5 Siamese 4
6 Sphynx 4

This table is meant to represent a category/subcategory relationship that can be represented as:

dog
- lab
- terrier
cat
- siamese
- sphynx

There is also a table named animals that stores information about the animal, including the breed.

Based on user input, the breed is set and could be specific to lab or stop at dog. (Theoretically this could be more than two levels deep, but I'm keeping it at two for this question.)

In the program that the user uses to view/modify this data, they will see two fields, one that would show the higher level (dog) and one that would show the more specific level (lab).

As far as actual columns on the table, what is the benefit of one column storing the most specific id given (2 if lab, 1 if dog) or two columns, one for each level?

As far as getting the information, a simple enough query can find the parent of any given id or determine if it doesn't have any level above it - so the information is accessible either way.

The column won't store the pretty name, only the numerical id referencing the breeds table, so readability in the database isn't too much a factor.