There are several types of objects in a system, and each has it's own table in the database. A user should be able to comment on any of them. How would you design the comments table(s)? I can think of a few options: One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc) Several comments tables, one for ea...
I'd like to know do I normalize a URL in python. For example, If I have a url string like : "http://www.example.com/foo goo/bar.html" I need a library in python that will transform the extra space (or any other non normalized character) to a proper URL. ...
I'm just wondering what the optimal solution is here. Say I have a normalized database. The primary key of the whole system is a varchar. What I'm wondering is should I relate this varchar to an int for normalization or leave it? It's simpler to leave as a varchar, but it might be more optimal For instance I can have People =========...
I'm architecting a new app at the moment, with a high read:write ratio. At my current employer we have lots of denormalised data on our tables for performance reasons. Is it better practice to have totally 3NF tables and then use indexed views to do all the denormalisation? Should I run queries against the tables or views? An example of...
I've got a table of hardware and a table of incidents. Each hardware has a unique tag, and the incidents are tied to the tag. How can I select all the hardware which has at least one incident listed as unresolved? I can't just do a join, because then if one piece of hardware had multiple unresolved issues, it would show up multiple tim...
I'm designing a database for an events management site - there is a table of venues and a table of events. Each event is in a venue (stores the venue's id) and each venue is in a city. It must be possible to search for event by city, should city be a field in the venues table (which could possibly result in duplicate cities due to misspe...
I have a coworker who is planning a database for a new app that will have several tables with over 30 fields each. Is this excessive? Maybe I'm just not enterprisey enough to understand. Edit: Also, a lot of the fields are option-type sort of things (like on a request form, would you like your widget to be yellow or green, he has a fie...
This is a simplified version of the problem. We have customers who send us lots of data and then query it. We are required by them to have several "public" ids they can query our data by. (Most want to query our system via the id they send along with the data, but not always). For simplicity, we'll call them "pid", "crid" and "music...
We have been working on a complex database and client interface for the last 18 months. We are regularly adding new functionnalities to this application, and it is now used by tens of users on a daily basis in all our offices, including sites and overseas. This is just to tell you it is a REAL application with a REAL database. Until now...
What is the fastest way to load data from flatfiles into a MySQL database, and then create the relations between the tables via foreign keys? For example... I have a flat file in the format: [INDIVIDUAL] [POP] [MARKER] [GENOTYPE] "INDIVIDUAL1", "CEU", "rs55555","AA" "INDIVIDUAL1", "CEU", "rs535454","GA" "INDIVIDUAL1", "CEU",...
Why do database guys go on about normalisation? What is it? How does it help? Is it even that important? Does it apply to anything outside of databases? ...
I have a system which is using phone numbers as unique identifiers. For this reason, I want to format all phone numbers as they come in using a normalized format. Because I have no control over my source data, I need to parse out these numbers myself and format them before adding them to my DB. I'm about to write a parser that can read ...
In the following table structure: Fruits ( fruit_id, fruitName ) Vegetables ( vegetable_id, vegetableName ) favoriteFoods ( food_id, foodName, type_id (References either a fruit or a vegetable) ) I realize that I could forgo using a foreign key constraint on the favoriteFoods table and then simply a...
When would a database design be described as overnormalized? Is this characterization an absolute one? Or is it dependent on the way it is used in the application? Thanks. ...
Normalization leads to many essential and desirable characteristics, including aesthetic pleasure. Besides it is also theoretically "correct". In this context, denormalization is applied as a compromise, a correction to achieve performance. Is there any reason other than performance that a database could be denormalized? ...
I have a table which consists of 200 Companies Stock prices for 5 years. This is one large table which consists of Company Name, Stock Open, High, Low, Close, Date I am now required to do some processing on the same and also let users [up to 10] access this database to fetch reports on different sets of parameters and queries. Should I...
Hi folks, EDIT1: Tried to clear the question up by renaming the tables AND their relationships. EDIT2: Please don't look at the what TYPE of data i'm holding in the three DB tables. They were made up on the fly. They are NOT my real world scenarios (and no, I can't talk about my real world data .. in fact it's 1 parent and 6 children, c...
I'm currently designing a database for a project. Now I'm debating with myself whether I have to create a look-up table for example 'civil status' data which can only contain fixed values like Single, Married, Separated, Widow/Widower. I'm pretty sure that no other values will be added in the future. Should I put these on a separate tabl...
I find this comes up a lot, and I'm not sure the best way to approach it. The question I have is how to make the decision between using foreign keys to lookup tables, or just taking the lookup table value and inserting it into the table requesting the data when you have tables that are mostly based on existing data. Points to keep in ...
Use case: User 1 uploads 100 company names (e.g. Microsoft, Bank of Sierra) User 2 uploads 100 company names (e.g. The Gap, Uservoice, Microsoft, Inc.) I want User 1's notion of Microsoft and User 2's notion of Microsoft to map to a centrally maintained entity with a unique index for Microsoft. If someone uploads a name which isn't i...