Mysql – Creating a normalised Twitter database and foreign keys

database-designMySQL

Hello I am creating a normalized Twitter DB for a project I am working on and wanted a bit of guidance on foreign keys.

I am curious about what the difference would be if for hashtags, symbols, user_mentions, urls and media, I made another field in each table called tweet_ID and the relationship changes to the primary key id in the tweet table to each tables tweet_id? The way I've modelled it means the tweets table has 5 extra fields for each tweet object.

Currently I have the schema designed as follows:

Are there glaring issues with the way I have implemented the foreign keys in the model this way?

Thank you!

Best Answer

So, for each tweet, you can have no more than one hashtag? That doesn't sound right to me.

For tweets and hashtags (and probably for the other four connected tables), I would create a "bridge" table, tweet_hashtag. This table would consist of:

tweet_id (foreign key to the tweets table)
hashtag_id (foreign key to the hashtagstable)
if appropriate, other columns that would apply only to the bridge table, if any (I can't think of any in this case, as I assume a tweet is created at a single moment in time; if tweets could be edited, then a created_on date/time column could be appropriate)
(Note: the combination of tweet_id and hashtag_id should be the primary key for this table, as you presumably should only note a given hashtag appears in a given tweet once. You can create a surrogate key (probably just an auto-increment integer or bigint value) if you really want to, or if you do need to be able to have multiple entries of the same hashtag for a single tweet for some reason)

This allows a given hashtag to be associated with many tweets, and a given tweet to have multiple (or no) hashtags.

As noted above, the same probably applies to the other four fields associated with the tweets table (user_mentions, urls, media, symbols).

Related Solutions

Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

Querying a big database

That is a very denormalized design, as you have pointed out yourself. Especially if there isn't always a 1 : 7 ratio of Phone Numbers to Flags. Depending on your business data, you could potentially have a vast amount of sparse data.

I'd recommend a thin, normalized structure like this:

create table Flag
(
    pkFlag int identity(1, 1) not null primary key clustered,
    FValue varchar(100) not null
)
go

create table PhoneNumber
(
    pkPhoneNumber int identity(1, 1) not null primary key clustered,
    PnValue varchar(20) not null
)
go

create table PhoneNumberFlag
(
    fkFlag int not null foreign key references Flag(pkFlag),
    fkPhoneNumber int not null foreign key references PhoneNumber(pkPhoneNumber)
)
go

This allows you to create a one-to-many relationship (or possibly a many-to-many relationship, I can't say for sure as you haven't explained the meaning of Flags or any part of the domain).

A simple query to get all flags for a particular phone number would be:

select 
    f.fValue
from PhoneNumber pn
inner join PhoneNumberFlag pnf
on pn.pkPhoneNumber = pnf.fkPhoneNumber
inner join Flag f
on f.pkFlag = pnf.fkFlag
where pn.PnValue = '555-555-5555'

As for the RDBMS you are looking to go with, that is very dependent on MANY factors. What's your environment? What do you already have? MySql is a very different type, as it is open source. Oracle, SQL Server, these are not. I'm not sure about Oracle, but SQL Server has a free edition (SQL Server Express) that you could leverage, but it is limited. RDBMS licensing can get costly very fast. There are too many questions that need answering before a sound recommendation on that front can be made.

Best Answer

Related Solutions

Constraint many to many table between two child tables

Querying a big database

Related Question