Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012

    Database design and terminology

    Somehow I stirred up a huge database project at work and I'm trying to come up with the best solution. If someone could give the name or some of the terminology so I could read more about the specific type of database I'm trying to design it would be really helpful. Just a little disclaimer I'm not a computer scientist I'm a double E, which is why I am pretty clueless on database design.

    So I have some data that looks like this.
    For example
    ID   SDate   EDate   P1 P2 P3 P4 P5 P....
    1   1/1/12    2/1/12   0   2   5   0   3  ....
    2   1/2/12    2/2/12   0   5   0   2   1  ....      
    3   1/3/12    2/3/12   0   2   0   3   1  ....      
    4   1/4/12    2/4/12    .    .    .    .   .      
    5   1/5/12    2/5/12    .    .    .    .   .   
    6   1/6/12    2/6/12
    The P columns are 256 but I would like to leave room to expand in case someone decides we need more columns and they are only relevant to me when they contain data. I would like to get all the records but I only want the columns that contain data in at least one of the records. On my example if I query records 1,2 and 3 I would only get columns P2 through P5 since P1 is always zero. However when I get record number 1 I would also need to get P4 since at least one of the records I queried had data for that column.
    Any ideas on how to design the best database for this type of records be would really appreciate it. Also some terminology regarding the type of DB I'm trying to design so I can read up on it.

  2. #2
    Join Date
    Dec 2007
    London, UK
    Unfortunately some obscurely named columns and a few numbers doesn't tell us much about the nature of your data or your basic requirements - certainly not enough to give sensible advice on database design.

    What does "huge" mean? If it means potentially expensive, time-consuming or very important to yourself or your organisation then I suggest you hire some help or find someone with more experience in database design. Database design mistakes can be costly to fix. Database designs also rarely live in isolation - they usually form part of a larger technical solution. The design of that solution and how the database fits into it is another important consideration.

    If you are willing to take on the risk of doing it yourself then I suggest you start by studying some books on database principles, design and development. Jumping right in and relying on design advice from forums like this one probably isn't a great strategy for success.

    Hope this helps.

  3. #3
    Join Date
    Nov 2004
    out on a limb
    for a start, seeign columns named p1, p2, p3, p4......Px suggests a design that is not normalised.
    if it were me I'd push the results to a sub table, identify the type of result (effectivley the P1 and its value with a FK to the current table. that way round you cna have as many results as you wish. bear in mind that some db engines will struggle with internal limits. ferinstance Access/JET has an internal limit of around 256 columns
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jun 2003
    What do the P columns represent?
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  5. #5
    Join Date
    Aug 2008
    Commit some time to reading on relational databases. Read up on the basic principles such as : relational operations , normalizations.

    Try and apply thses principles to create an architectural diagram.

    The impact of bad design can be catastrophic for business outage. For example, key violations, scalability issues , performance issues can all come from poor design decisions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts