I've crossed this problem in, oh, every application I've ever written. I'd like a final consensus answer! Is this the most normalized/efficient/proper way to store contact methods or any other data along this pattern in a database? Contact { PK ContactId, ... } ContactContactMethod {PK FK ContactId, PK FK ContactMethodId, Key, Commen...
I was thinking about some stuff lately and I was wondering what would be the RIGHT way to do something like the following scenario (I'm sure it is a quite common thing for DB guys to do something like it). Let's say you have a products table, something like this (MySQL): CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increme...
I would like to normalize data in a DataTable insertRows without a key. To do that I need to identify and mark duplicate records by finding their ID (import_id). Afterwards I will select only the distinct ones. The approach I am thinking of is to compare each row against all rows in that DataTable insertRows The columns in the DataTa...
The current project I inherited mainly revolves around one unnormalized table. There are some attempts at normalization but the necessary constraints weren't put in place. Example: In the Project table, there is a client name (among other values) and there is also a clients table which just contains client names [no keys anywhere]. T...
My database designs have previously been rather linear, so I'm getting stumped on what is probably a very easy to solve problem. I have a table of "POSTS", which contain posts which may be a child of either a "CATEGORY" or a "TOPIC". What would be the best way to define the foreign key(s) for the "POSTS" table? I suppose I could have ...
I have three denormalized tables that I have to take at face value (data comes from some external resource). The three tables have different definitions, but they each describe the same object from different perspectives. object1 A B object2 A object3 B C object4 C The only commonality between these tables i...
Ok, so here is a problem analogous to my problem (I'll elaborate on the real problem below, but I think this analogy will be easier to understand). I have a strange two-sided coin that only comes up heads (randomly) 1 in every 1,001 tosses (the remainder being tails). In other words, for every 1,000 tails I see, there will be 1 heads. ...
Hey everyone, I am trying to really wrap my head around the database normalization concept. I understand that it is used to avoid duplicate/redundant data, and it involves creating separate tables for certain things, but I'm not quite sure I understand 1NF, 2NF and 3NF fully. I have read some of the questions on here regarding normaliza...
I'm looking for a way to turn user input into normalized data fit for calculation. The input is some length of time, and is coming from Twitter, so it's string only. Ideally I'd love these results: an hour and a half --> 01:30 27.52 --> 00:28 5:24 --> 05:24 Is this is a particularly difficult thing to do? I...
I have a couple of tables in a web application I'm coding in PHP, and I would like to know if this would be good pratice. CREATE TABLE `products`( `product_id` int NOT NULL auto_increment, `name` varchar(255) NOT NULL, `variations` varchar(255) default NULL, PRIMARY KEY (`product_id`) ) CREATE TABLE `variations`( `variation_...
Are there examples of Relational tables which are in 3NF or 4NF but not in Domain Key Normal Form? ...
I was wondering is this a good design, assuming the tables as follows ADDRESS(id, address, city_fk, stateFK, countryFK), CITY(id, name, stateFK, countryFK), STATE(id, name, countryFK), COUNTRY(id, name) Notice how country fk is repeated in 3 tables? and state fk repeated in 2 tables? Can anyone tell me if this is a good design? If s...
I'm trying to sort out the database behind my company's website. We deal with both Sites and Residents. At the moment the Site Reference is included in the Residents table as a foreign key but, obviously, this is only in 1NF. Site references are of the form nnnnn (12345, for example). Each resident has their own reference within the Si...
Hi, I'm very new to databases and I have a quick question. How would I design my MySQL database if I have these fields: ID, lat, long, date - multiple dates, time - multiple times I know I should put it into two tables, right? And how would those two tables look? Thanks! ...
Hello, I have an application where I'll have repeating events. So an event can repeat by day, "every n days", by week, "every n weeks on Mon/Tue/Wed/etc", and by month, "every n months on the 1st,2nd,3rd,etc". What is the best way to handle this from a table design perspective? I can think of two ways but I'm not sure which one is bett...
You usually normalize a database to avoid data redundancy. It's easy to see in a table full of names that there is plenty of redundancy. If your goal is to create a catalog of the names of every person on the planet (good luck), I can see how normalizing names could be beneficial. But in the context of the average business database is it...
Anyone now what is a draft design of a database??? Is it the design process and steps like: Determine the purpose of your database Find and organize the information required Divide the information into tables Turn information items into columns Specify primary keys Set up the table relationships Refine your design Apply ...
Is it possible to represent a composite key functional dependency of a non key column in fifth normal form? I have three tables, users ---------- id name events ---------- id name events_users --------------------------- id user_id event_id participation_type (ENUM) As I know, the 5th normal form needs the tables to be represented ...
So I’ve been working on this project at work where I’m coding a php website that interacts with a database I have no control over. The database was “designed” by a co-worker that has been with the company many more years then I have; so in the end decisions are left for them to decide. When I was first pulled aboard on this project I w...
I have a 2 tables: a membership table and mailing list table. [Members] Username Password EmailAddress MailingListOptIn [bit] Planes [bit] Boats [bit] Cars [bit] The fruits columns are user preferences. and [MailingList] EmailAddress MailingListOptIn A mailinglist'er can opt to receive newsletters but may not necessarily be a ...