sql - Basic question: how to properly redesign this schema -

-

i hopping on project sits on top of sql server 2008 db seems inefficient schema me. however, i'm not expert @ sql, seeking guidance.

in general, schema has tables this:

id | | b

  • id unique identifier
  • a contains text, such animal names. there's little variety; maybe 3-4 different values in thousands of rows. vary time, still small set.
  • b 1 of 2 options, stored text. set finite.

my questions follows:

  • should create table names contained in a, id , value, , set id primary key? or should put index on column in table? right now, list of a's, "select distinct(a) table" seems inefficient me.
  • the table has multitude of columns properties of a. like: color, age, weight, etc. think better suited in separate table with: id, animalid, property, value. each property unique animal, i'm not sure how schema enforce (the current schema implies it's column, can have 1 value each property).

right db readable human, size growing fast , feel design inefficient. there not index @ anywhere. said i'm not pro, read more on subject. goal have fast system. advice!

this sounds database might represent veterinary clinic.

if table describe represents various patients (animals) come clinic, having properties specific them best on primary table. but, column "a" contains species name, might worthwhile link secondary table save on redundancy of storing names:

for example:

patients -------- id  name   speciesid   color         dob         weight 1   spot   1           black/white   2008-01-01  20  species ------- id   species 1    cocker spaniel

if main table should instead grouped customer or owner, may want add animals table , link it:

customers --------- id   name 1    john q. sample  animals ------- id   customerid   speciesid   name    color        dob          weight 1    1            1           spot    black/white  2008-01-01   20  ...

as original column b, consider converting boolean (bit) if need store 2 states. barring that, consider char store fixed number of characters.


Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more