Home » Developer & Programmer » Designer » Data Modelling (Oracle 11G Unix)
Data Modelling [message #609488] Fri, 07 March 2014 06:36 Go to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
Hi,

I have been given a task to create data model for my organisation's Warehouse, I decided to build Start Schema but when i see Customer Dimension, data comes from several sources. Our aim is to deduplicate the customers. and need to keep some of the fields from each source in warehouse dimension table. If you take Social as source, Customer may come through several social sites Facebook, Twitter, Instagram and etc. need to keep track of each social site as well. Geographical detail come from sales source, Loyalty score comes from CRM system. we have e-mail id as Key in all the systems.

My table would be look like below:
Customerid, name, gender,zipcode, Facebook, Twitter,Instagram,Geographical Score, Loyalty Score and etc.]


Can we split this into several tables and reduce the null values?

Can you please advise on this?
Re: Data Modelling [message #609494 is a reply to message #609488] Fri, 07 March 2014 10:43 Go to previous messageGo to next message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Right; create another table and use referential integrity. For example:
-- This table would contain values like 1 - Facebook, 2 - Twitter, etc.
create table source
  (source_id      number(2)    constraint pk_source primary key,
   source_name    varchar2(20) not null
  );
  
create table customer
  (customer_id    number(5)    constraint pk_cust primary key,
   customer_name  varchar2(30) not null,
   gender         varchar2(1),
   --
   -- The following column will store values that exist in the SOURCE table
   source_id      number(2)    constraint fk_cust_source
                                 references source (source_id)
  );
Re: Data Modelling [message #609497 is a reply to message #609494] Fri, 07 March 2014 11:03 Go to previous messageGo to next message
nagaraju.ch
Messages: 98
Registered: July 2007
Location: bangalore
Member
AS i mentioned there may be customer from multiple sources, Eg: customer1 from Facebook and Twitter. In that case Primary key doesn't work right?
Re: Data Modelling [message #609498 is a reply to message #609497] Fri, 07 March 2014 11:06 Go to previous message
Littlefoot
Messages: 19474
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As I said, this is just "for example"; I wasn't paying attention to CUSTOMER table primary key - set it to whatever you want (if anything). I just wanted to show how to you

split this into several tables and reduce the null values
Previous Topic: What is your data modeler?
Next Topic: Fact Table
Goto Forum:
  


Current Time: Thu Aug 21 15:21:27 CDT 2014

Total time taken to generate the page: 0.13045 seconds