Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    16

    Client Router Tracking Database

    I am putting together a database for tracking our client routers, their locations, and contacts... This is the conceptual design thus far, and I wanted to get input from others here, as this is my first database design.

    As an fyi, one client can have many locations, and from time to time a single location can have more than one router, and often many locations can share the same contact, but not always. I have tried to express that here in this diagram.

    I would like to know if my relationships look correct, and any other suggestions, criticisms, etc...

    Please note: The unlinked tables at the bottom are planned to be used only to fill web based drop downs.. I was not planning to link them directly to any tables/columns.
    Attached Thumbnails Attached Thumbnails router_tracking.png  
    Last edited by rokudan; 03-06-10 at 21:13.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by rokudan
    any other suggestions, criticisms, etc...
    I know nothing about routers etc so you can take this with a pinch of salt:
    • Is it correct that more than one client contact cannot work at the same location?
    • Your ClientRouter table uses clientRtrModel to access the RouterModels table and not the id field.
    • There is no table for the foreign keys ClientRtrAccCktId, ClientRtrAccIbrId or ClientRtrAccLecId.
    • The ClientRtrActive field is varchar(3) which implies you're going to store "Yes" and "No" in it - if so then it's better to use boolean.
    • ClientId is char(3) which seems quite small - wouldn't it be better to just have int to allow for more clients.
    • ClientConEmail is varchar(60) - this is quite adequate but email addresses can legally be bigger than this (but rarely are).

  3. #3
    Join Date
    Mar 2010
    Posts
    16
    Quote Originally Posted by mike_bike_kite View Post
    I know nothing about routers etc so you can take this with a pinch of salt:
    • Is it correct that more than one client contact cannot work at the same location?
    • Your ClientRouter table uses clientRtrModel to access the RouterModels table and not the id field.
    • There is no table for the foreign keys ClientRtrAccCktId, ClientRtrAccIbrId or ClientRtrAccLecId.
    • The ClientRtrActive field is varchar(3) which implies you're going to store "Yes" and "No" in it - if so then it's better to use boolean.
    • ClientId is char(3) which seems quite small - wouldn't it be better to just have int to allow for more clients.
    • ClientConEmail is varchar(60) - this is quite adequate but email addresses can legally be bigger than this (but rarely are).
    Hey Mike, thanks for he reply... I know nothing about databases, so that makes us even!!

    In Order:

    1. No, there could be more than one contact at each location, and as well there are cases when the same contact could be the contact for multiple locations, but of the same client.

    2. The reference from ClientRouters.ClientRtrModel is not an actual relationship.. The RouterModels table is only included to populate a web form drop box, so I can fill in the field in the ClientRouters table. I am not actually linking the tables, unless perhaps I should be? Same with the other two bottom tables, they are not linked, rather used to populate drop boxes, in which case I will use the data to write into the record.

    3. I think perhaps using Id at the end of the circuits, is confusing. They refer to Circuit ID's, not really ID's in another table.. For sake of being uniform, I will rename them to ClientRtrAccCkt, etc, and drop the Id.

    4. It is correct I plan to use Yes or No for the Active field, same with the Clients table for ClientActive field. What does using Boolean gain me over varchar? Sorry for the ignorance on my part..

    5. I chose to use char(3) for the ClientId field as we use only numbers 001 thru 999. Also, I want to keep the leading zeros if it is a client like 094, or 077, and it is my understanding that using a INT would strip the leading zeros, since it is a number field.. I used a text based field for Zip code field as well because of this, since there are zips that start with 0's. I think...

    6. I shall bump the email size up a bit, being a varchar, it isn't going to hurt afaik.


    Thanks again for the input. I am hoping to get this done right the first time, or at least close, so after it has been deployed I dont have to redesign the whole thing two years down the road because I didn't design for being able to add/grow.

  4. #4
    Join Date
    Mar 2010
    Posts
    16
    By the way, I am attaching an updated diagram to reflect the Ckt changes, and email length.. As well, I have changed the relationship of the ClientContacts table by removing the link to the Clients table, as I was told it doesn't need to be there since it is linked to the ClientLocations table, which is linked to the clients table..


    Also, you can check out the web piece I have so far at http://web.sitepros.net/fiserv

    I only have one faux client in the tables so far, you can search the name value greg or the number value 777. Not all the fields are populated with info, but I wanted to at least get the web format up..
    Attached Thumbnails Attached Thumbnails router_tracking2.png  
    Last edited by rokudan; 03-07-10 at 07:18.

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by rokudan View Post
    1. No, there could be more than one contact at each location, and as well there are cases when the same contact could be the contact for multiple locations, but of the same client.
    I can't see how your db would hold this - imagine that fred and pete bother worked at location x - how would you store this in the db as it stands? I believe you should have a locId in the client contacts table.

    Quote Originally Posted by rokudan View Post
    2. The reference from ClientRouters.ClientRtrModel is not an actual relationship.. The RouterModels table is only included to populate a web form drop box, so I can fill in the field in the ClientRouters table. I am not actually linking the tables, unless perhaps I should be? Same with the other two bottom tables, they are not linked, rather used to populate drop boxes, in which case I will use the data to write into the record.
    I think you should be. Look up "foreign keys" in google.

    Quote Originally Posted by rokudan View Post
    4. It is correct I plan to use Yes or No for the Active field, same with the Clients table for ClientActive field. What does using Boolean gain me over varchar? Sorry for the ignorance on my part..

    Booleans hold true false values. eYs and no are true or false values. If you're going to have other types of value then it's better to stay with varchar. It's really a matter of taste but it stops code like the following :
    Code:
    -- as a string
    select *
    from Clients
    where ClientActive = "Yes" or ClientActive = "yes" 
         or ClientActive = "YES" or ClientActive = "Y"
    
    -- as a boolean
    select *
    from Clients
    where ClientActive

    Quote Originally Posted by rokudan View Post
    5. I chose to use char(3) for the ClientId field as we use only numbers 001 thru 999. Also, I want to keep the leading zeros if it is a client like 094, or 077, and it is my understanding that using a INT would strip the leading zeros, since it is a number field.. I used a text based field for Zip code field as well because of this, since there are zips that start with 0's. I think...
    It also limits you to 1000 clients - perhaps this is fine but it does seem an unnecessary limit.

    Quote Originally Posted by rokudan View Post
    I am hoping to get this done right the first time, or at least close, so after it has been deployed I dont have to redesign the whole thing two years down the road because I didn't design for being able to add/grow.
    Databases always need changing to keep up with the real world they are modelling - the aim is to make your design easy to growth without having to throw everything out.

    PS I also think it would be better to have your field names start in lower case to differentiate them from table names.

  6. #6
    Join Date
    Mar 2010
    Posts
    16
    Quote Originally Posted by mike_bike_kite View Post
    I can't see how your db would hold this - imagine that fred and pete bother worked at location x - how would you store this in the db as it stands? I believe you should have a locId in the client contacts table.
    I was thinking about creating a primary contact and a backup contact field in the ClientLocations table, that would point to two possible Contacts.. But perhaps, instead, I should do what you suggest and reverse the setup and link the Contact to the Location, and then on my results page put together a second query/recordset to display all the possible contacts. Does this sound like a better solution? Of course, this would present a problem of linking a contact to more than one location, no?


    Quote Originally Posted by mike_bike_kite View Post
    I think you should be. Look up "foreign keys" in google.
    I've been trying to decide on how to handle those bottom tables. The problem I had is that when when I linked for example the MaskId table to ClientRouters.ClintRtrLanMask for example, and to the Mask value in ClientAsns, that when I try to throw up the results page and match the Id to the value, I was having some trouble. The routers table I could make work just fine, since it is only tied to one field on in table. But the maskid field was linked to two tables, both output in the same results page. Does it make sense to use the masks table in the manner I have currently, but link the routermodel table as you say?

    Quote Originally Posted by mike_bike_kite View Post

    Booleans hold true false values. eYs and no are true or false values. If you're going to have other types of value then it's better to stay with varchar. It's really a matter of taste but it stops code like the following :
    Code:
    -- as a string
    select *
    from Clients
    where ClientActive = "Yes" or ClientActive = "yes" 
         or ClientActive = "YES" or ClientActive = "Y"
    
    -- as a boolean
    select *
    from Clients
    where ClientActive
    Now this I like! Thanks!!


    Quote Originally Posted by mike_bike_kite View Post
    It also limits you to 1000 clients - perhaps this is fine but it does seem an unnecessary limit.
    At the moment, and like very forseeable future, we only have a couple hundred clients, and they rarely change, maybe a couple changes a year.. So I figured for now, I would limit the field, and if need be in the future I could always change it, correct?


    Quote Originally Posted by mike_bike_kite View Post
    Databases always need changing to keep up with the real world they are modelling - the aim is to make your design easy to growth without having to throw everything out.
    I appreciate you taking the time and helping a newbie at this. Having not done this before, it has been a bit of a challenge, even to just get the concept and thought process down.
    Last edited by rokudan; 03-07-10 at 15:56.

  7. #7
    Join Date
    Mar 2010
    Posts
    16
    I think I might have figured out what I need to do. I've updated the schema diagram to include the change. I've created a bridge table between ClientContacts and ClientLocations.

    Does this seem like what I need to do?
    Attached Thumbnails Attached Thumbnails router_tracking3.png  

Posting Permissions

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