SQL Table Layout

25 pts.
Tags:
SQL 2005
SQL tables
Hi

I am creating a database. In my legacy system I had what we called a tables system where coded data could be expanded into different values

 

eg

0 = No

1 = Yes

I tried to create this in SQL by having a properties table and then relating the other tables to the property table. However this wont work as the property table contains many/many properties. When I try and establish a relation between by table and the property table, I cant easily do this

 

for example

Inventory Table

itm_no

itm_des

itm_type (0,1,2,3)

 

Debtor Table

dbt_no

dbt_name

dbt_type (0,1,2,3)

 

Property_Table

itm_type, 0, Stocked

itm_type, 1, Non Stocked

itm_type, 2, Replaced

itm_type, 3, Legacy

dbt_type, 0, Active

dbt_type, 1, Hold

dbt_type, 2, In Active

My database has many table with heaps of properties. I cannot establish a relationship between the itm_table and the property table because I cant specify the itm_type field in the relation

How should this be handled?



Software/Hardware used:
SQL 2005

Answer Wiki

Thanks. We'll let you know when a new response is added.

I like to refer to these sorts of data stores as a “table table” – where we take the data that might have been in a table (in a COBOL program for example) and put the data into a table (in an RDBMS)..

if you want all of your values in ONE table – then you need to add columns to your “table table”

Inventory Table

itm_no
itm_des
itm_type (0,1,2,3)

Debtor Table
dbt_no
dbt_name
dbt_type (0,1,2,3)

TABLE TABLE
table_name_containing_the_code
column_name_containing_the_code
code_value
description
effective_date
end_date
userid_who_changed_this_row

This one “table table” might become a hot spot with lots and lots of I/O, depending on your application(s). So there are other considerations … , size of this “table table”, one “table table” for INVENTORY, one for DEBTOR … load the “table table” into memory and pin it there to avoid too much physical I/O,

Steve

Discuss This Question: 6  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Another option could be splitting the properties table:
    PROPERTY_GROUP
    GroupId		(PK)
    GroudDescription
    
    PROPERTY_TABLE
    GroupId		(PK)
    PropertyId	(PK)
    PropertyValue
    Then, your other tables would need to include the GroupId and the PropertyId. The option of having 1 table for each group of properties is also a good one, and both have pros and cons.
    69,475 pointsBadges:
    report
  • Philsal
    I guess the biggest issue I have is that I want to add relations into the tables so that tools such as crystal reports can just populate the links between tables. It seems to me that this means the every property in the base tables require two fields. There are going to be many hundreds of these so it doesnt seem to me to be efficient way of handling. In our legacy system this was handled automatically
    25 pointsBadges:
    report
  • carlosdl
    "In our legacy system this was handled automatically" automatically, how ? Something may look as 'automatic' to the users, but internally there must be a process that automates it. How was that managed internally ? On the other hand, how about the one look up table for each group of properties approach (which is the option I would prefer) ?
    69,475 pointsBadges:
    report
  • Philsal
    Hi Yes we programmed it in our legacy system (which is not sql). We could have a seperate table per group but there are 396 different properties in the database. Would this make it unwieldy?
    25 pointsBadges:
    report
  • carlosdl
    I don't think that would make it unwieldy. The number of tables is not really important, and from the database design point of view, that would be the recommended approach.
    69,475 pointsBadges:
    report
  • Philsal
    [...] SQL Table Layout, asked by Philsal and answered by Meandyou and [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following