Database Design Microsoft SQL Server

Question

Was thinking of creating a small application in c# but would like to hear some feedback on approaching my project with this method.

Basically I will have 2 tables in MSSQL

    These are just examples....

 **TableType
      TableTypeID     
      Type


    TableInfo
      InfoID
      TableTypeID
      Name
      Color
      Size
      TagNumber**

So when a user wants to create a new table say birds the value would go into the tabletype create an autoid and then have the Type as birds. The name for the bird, Color, size would go into the TableInfo Table.

But now lets say that they would like to create a new table called auto. So again auto would go into tabletype under the type column and then the auto's color, size, tagnumber would all go into the next table.

Is this the right way to approach this or will this cause a lot of issues with programming in the future

Thanks

You may want to look at entity/attribute/value as a possible design. And have a look at database normalization. Then figure out what you really want to do. — HABO
– HABO, Commented Sep 3, 2012 at 18:24
So, what happens when a user wants to create a table Parrots (which as everyone knows have more than one colour)? Or a table Cities (which have no colour)? Or a table Planets (which have also temperature, density and distance from Earth)? ... — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Sep 3, 2012 at 21:54

Neil McGuigan · Accepted Answer · 2012-09-03 21:20:01Z

2

This is a weird misuse of Table Inheritance, and it's not a good idea.

If you want to store info about birds in a relational database, you should have a table called Bird(s) that stores info only about birds. And have a different one for Autos.

You could consider using table inheritance for related types, such as Tits and Swallows

edited Sep 3, 2012 at 21:20

answered Sep 3, 2012 at 17:50

Neil McGuigan

48.5k12 gold badges130 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ibondre · Accepted Answer · 2012-09-03 17:49:04Z

0

Yes, this could be one way of doing based on the information you have provided.

I am guessing you will have different table types added overtime, may be even dynamically, and your code will map to a different object type on the business logic side based on those table types.

your names are confusing though, instead of using TableType, perhaps a better name could be RecordType and RecordDetails or RecordInfo.

answered Sep 3, 2012 at 17:49

ibondre

637 bronze badges

Collectives™ on Stack Overflow

Database Design Microsoft SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related