Optimizing Existing Database

[drunyan0824]

Recently, I made a database to keep track of Network, Audio and Video cables that my co-workers and I have installed in our studio. As I have been using the database, I have ran into some issues with the operation of the database. The issues that I want to fix are with data entry, how records are stored and how we can access the data. The main problems that I want to fix are:

1. Data entry redundancy: We categorize all the cable we run based on the type of data/signal that the cable carries. e.g. High definition video is categorized as HDV and Standard definition is categorized as SDV. Then each cable is assigned a four digit number. So the cable is ultimately labeled something like HDV1004 or SDV0507. At this point when a user adds a cable to the database, the user has to select the cable's category from a combo box and then type on the cables' number. So you have to put in the cable category twice. I would like to change this so that when you select the category the record would be written to a table that is for that specific to that category. So the database would have a separate table for HDV cables and SDV cables.

2. Deletion of Cables: Currently we delete records from the database when we remove cables and reuse the cable numbers. This has caused some problems. Thankfully we have only had issues with user error reusing cable numbers and not any problems with the database itself. But I am sure those are coming. What do is when a cable is 'deleted' from the database that the cable number and all the related information would be moved to a table of deleted cables and we would stop reusing cable numbers.



3. There is some things that we are recording in the database that don't really make any sense. Like we keep track of whether or not a cable is connected or not. The problem with doing that is we don't usually pre-run anything unit we are actually installing equipment and then no one is going into the database to update the information that a specific cable has been disconnected or not. So I want to get completely get rid of that.

My biggest question and concern at this point is how do I make the changes to my database with out screwing everything up? Can I make the changes that I want in the current database or should I create a new database and import the data from the old database?

[Micron]

I suspect the best advice would be given by looking at what you already have. Or there is the fact that you have the ability to work on something new while the old version can still be used, as long as getting your data into the right places is possible because you properly designed the new one. Regardless, I think the new version should not re-use cable numbers.

Is this the first time the notion of deleting records has come up, or by chance were you already advised to not do that?

[drunyan0824]

I was advised against deleting records but it a function that my boss wanted and at that time I didn't really have the confidence to tell him that it was a bad idea. I am pretty confident that I could create a new database relatively quickly, but I am not sure how I would import the data from the old database into the new database. I have attached the current database that I want to change.